Convert Rows to columns using ‘Pivot’ in SQL Server

If you are using SQL Server 2014+, 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

1 2 3 4 5 6

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;

1 2 3 4 5 6 7 8 9 10 11

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);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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);