CREATE TABLE #yt ( [Store] NVARCHAR (50), [Week] NVARCHAR...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Theoretical Computer Science

Q&A for theoretical computer scientists and researchers in related fields

CREATE TABLE #yt 
(
[Store] NVARCHAR (50), 
  [Week] NVARCHAR (50), 
  [xCount] int
);
    
INSERT INTO #yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    ('001431A', 2017-01, 96),
    ('001431A', 2017-02, 138),
    ('001431A', 2017-03, 37),
    ('001431A', 2017-04, 59),
    ('001431A', 2017-05, 282),
    ('001431A', 2017-06, 212),
    ('001431A', 2017-07, 78),
    ('001431A', 2017-08, 97),
    ('001431A', 2017-09, 60),
    ('001431A', 2017-10, 123),
    ('001431A', 2017-11, 220),
    ('001431A', 2017-12, 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);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results