SQL Server 2005+, then you can use the PIVOT

Submitted by erick on Sat, 08/19/2017 - 19:43

If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.

It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

If your values are known, then you will hard-code the query:

 

Store Week xCount ------- ---- ------ 102 1 96 101 1 138 105 1 37 109 1 59 101 2 282 102 2 212 105 2 78 109 2 97 105 3 60 102 3 123 101 3 220 109 3 87

 

you would like it to come out as a pivot table, like this:

Store 1 2 3 4 5 6.... ----- 101 138 282 220 102 96 212 123 105 37 109

You need the next code:

select * from ( select store, week, xCount from yt ) src pivot ( sum(xcount) for week in ([1], [2], [3]) ) piv;

 

Then if you need to generate the week number dynamically, your code will be:

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(Week) from yt group by Week order by Week FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT store,' + @cols + ' from ( select store, week, xCount from yt ) x pivot ( sum(xCount) for week in (' + @cols + ') ) p ' execute(@query);