I have an issue where I am trying to use Case statement to change the Pivot column in SQL Server.
pivot
(
max(col)
for (CASE WHEN @NeedBycolumn = 0 THEN column1 ELSE column2 END) in (' + @cols + ')
) p '
To illustrate my issue I am using the dummy data and query from this answer here:
https://stackoverflow.com/a/10404455/5686930
So here is the sample dataset and query that I am using
DECLARE @NeedByCategory BIT = 1
create table temp
(
date datetime,
category1 varchar(3),
category2 varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 'DEF', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 'ABC', 500.00)
insert into temp values ('2/1/2012', 'GHI', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 'ABC', 700.00)
insert into temp values ('3/1/2012', 'ABC', 'DEF', 1100.00)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME((CASE WHEN @NeedByCategory = 0 THEN category1 ELSE category2 END))
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT date, ' + @cols + ' from
(
select date
, amount
, category1
, category2
from temp
) x
pivot
(
max(amount)
for category2 in (' + @cols + ')
) p '
execute(@query)
drop table temp
In the above query I am using case statement based on a parameter value to change the column that I am passing at
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME((CASE WHEN @NeedByCategory = 0 THEN category1 ELSE category2 END))
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
So at the same time I want to use case statement in Pivot part of the query to change the column name dynamically, but it's throwing me an error and I can't figure out what's going wrong.
This is the code:
DECLARE @NeedByCategory BIT = 1
create table temp
(
date datetime,
category1 varchar(3),
category2 varchar(3),
amount money
)
insert into temp values ('1/1/2012', 'ABC', 'DEF', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 'ABC', 500.00)
insert into temp values ('2/1/2012', 'GHI', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 'ABC', 700.00)
insert into temp values ('3/1/2012', 'ABC', 'DEF', 1100.00)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME((CASE WHEN @NeedByCategory = 0 THEN category1 ELSE category2 END))
FROM temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT date, ' + @cols + ' from
(
select date
, amount
, category1
, category2
from temp
) x
pivot
(
max(amount)
for (CASE WHEN @NeedByCategory = 0 THEN category1 ELSE category2 END) in (' + @cols + ')
) p '
execute(@query)
drop table temp
This is the error:
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '('.
If someone can take a look at it and see if it is even possible or not, that would be great. If there is any way else to do it, please let me know.
Thank you all for your time.
