How do you you know that C is the sum of A and B and not any of the other values? How do you know that O shouldn't sum the...

How would you expect to differentiate both product groups?

You need to alias your max(val) column. 😉

Could you use something like this?

J.[Week] BETWEEN ISNULL(@WeekBegin, -1) AND ISNULL(@WeekEnd, 9999999)

Functions on parameters don't affect SARGability.

Can you give an example?

You have 2 options:

1. Use a UNION ALL with one query including a detail of the products and another one aggregating the information.

2. Use GROUPING SETS

SELECT ISNULL( product, 'O') product,...

What do you mean by being used?

In that precise moment? In the last days? That have data? That are included in stored procedures, functions or views?

I'm sure your code can get many improvements. Could you post some sample data and DDL to be able to test?

Have you checked the execution plans with both compatibility levels? You should be getting different plans.

Table variables don't have statistics and the optimizer always treats them as if they have...

Try this:

DECLARE @ColumnName2 NVARCHAR(MAX)='East,Central,West';

SET @ColumnName2 = REPLACE(@ColumnName2, ',', '],[');

SELECT @ColumnName2

Why aren't you parametrizing your dynamic query?

Your column list for the pivot is wrong. You set it like this:

DECLARE @ColumnName2 NVARCHAR(MAX)='East,Central,West';

And use it in here:

PIVOT(

...

This is an option. I'm using CROSS APPLY to reduce code at the formulas. 😉

SELECT EmailAddr,

Fullname,

LEFT(Fullname, CHARINDEX('.', Fullname) - 1) FirstName,

...

You're describing 3 datasets, which is a terrible idea as you'll have problems managing them.

Instead, you need a single dataset that can be formatted in the presentation layer which I...

Note that if you format your date it won't be a date any more and it will be a string. String dates will give you problems if you try to...

I'm sure I've been bitten by this issue before, but I'm trying to replicate it without success.

Maybe it has something to do with this:

From BOL

The size of the string is...