Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software
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); 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);