I am using MS SQL SERVER 2008 and I want to convert following dynamic pivot query into static pivot
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Weeks)
from MyTestTable
group by Weeks
order by Weeks
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Month_year,' + @cols + ' from
(
select Month_year, weeks, Xcount
from MyTestTable
) x
pivot
(
sum(xCount)
for Weeks in (' + @cols + ')
) p '
execute(@query);
Please help me.
What I have tried:
I have tried to make following query but occurs error in "For weeks in ()" pivot statement (last to lines of query).
SELECT Month_year,STUFF((SELECT ',' + QUOTENAME(Weeks) from MyTestTable group by Weeks
order by Weeks FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
from
(
select Month_year, weeks, Xcount
from MyTestTable
) x
pivot
(
sum(xCount)
for Weeks in (STUFF((SELECT ',' + QUOTENAME(Weeks) from MyTestTable group by Weeks
order by Weeks FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''))
) p