Tuesday, October 30, 2018

SQL Pivot Table


A dynamic SQL pivot table can rearrange SQL output.  This can be useful if trying to format for Excel.


//Create a table variable
CREATE TABLE #yt
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

//Insert some values into the table
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);

//Generate the format of the top column values for the pivot table.  Week in this example.
DECLARE @cols 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,'')

//Generate the SQL query with the dynamic column data
DECLARE @query  AS NVARCHAR(MAX)
SET @query = 'select * 
from 
(
  select store, week, xCount
  from #yt
) src
pivot
(
  sum(xcount)
  for week in (' + @cols + ')
) piv;'

//Execute the query
exec(@query)

//The results will display with the store and week as the row/column values
store123
101138282220
10296212123
105377860
109599787


No comments: