Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.4k points)
edited by

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

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 11, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
asked Nov 21, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
asked Jul 25, 2019 in SQL by Tech4ever (20.3k points)

Browse Categories

...