0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

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),
    (105, 4, 60),
    (102, 4, 123),
    (101, 4, 220),
    (109, 5, 900),
    (101, 6, 1000);
    
DECLARE @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Week)  FROM #yt  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'select *
from 
(
  select store, week, xCount
  from #yt
) src
pivot
(
  sum(xcount)
  for week in (' + @cols + ')
) piv;'

execute(@query)

fork query

edited jul 11 20

created jul 11 20 anonymous