Convert Rows to columns using 'Pivot' in SQL Server

Question

I have read the stuff on MS pivot tables and I am still having problems getting this correct.

I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type. Also the Week numbers are dynamic, the store numbers are static.

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 

I 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 

Store numbers down the side and weeks across the top.

Answer 1

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.

First up, here are some quick table definitions and data for use:

CREATE TABLE yt  (   [Store] int,    [Week] int,    [xCount] int );      INSERT INTO yt (   [Store],    [Week], [xCount] ) VALUES     (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); 

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

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

See SQL Demo

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

See SQL Demo.

The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:

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

Answer 2

This is for dynamic # of weeks.

Full example here:SQL Dynamic Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX)  --Get distinct values of the PIVOT Column  SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week) FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks  --Prepare the PIVOT query using the dynamic  SET @DynamicPivotQuery =    N'SELECT Store, ' + @ColumnName + '      FROM #StoreSales     PIVOT(SUM(xCount)            FOR Week IN (' + @ColumnName + ')) AS PVTTable' --Execute the Dynamic Pivot Query EXEC sp_executesql @DynamicPivotQuery