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

In SQL Server 2005+, to transform the data from rows into columns you can use the PIVOT function.

It seems like you will have to use the dynamic sql. If the weeks used are unknown but it is easier to see, then the correct code using a hard-coded version is as follows.

Following are the quick table definitions and data for use :

QUERY:

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 the values are known, then your hard-code query will be like this.

QUERY:

select *

from (select store, week, xCount from yt) src

pivot

(sum(xcount)for week in ([1], [2], [3])) piv;

 If you want to generate the week number dynamically, then your code will be like this:

QUERY:

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

 You can also go through this SQL Demo

Dynamic version will generate the list of week numbers which should be converted to columns.

Output is as follows:

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