SQL Server Pivots: Displaying row values to column headers

Question

I have a table (items) which is in the following format:

ITEMNO | WEEKNO | VALUE  
A1234  |   1    |  805  
A2345  |   2    |  14.50  
A3547  |   2    |  1396.70  
A2208  |   1    |  17.65  
A4326  |   6    |  19.99

It's a table which shows the value of sales for items in a given week.

The results or what I want to display in a table format is the item number in a row followed by columns for each week containing the values, e.g.

ITEMNO | WK1 | WK2 | WK3 | WK4 | WK5  ...etc up to 52  
A1234  | 805 | 345 | 234 | 12  | 10   ...etc up to 52  
A2345  |  23 | 12  | 456 | 34  | 99   ...etc up to 52  
A3456  | 234 | 123 | 34  | 25  | 190  ...etc up to 52

Although I've 52...so I've only data for up to week9 but that will increase with time.

So basically what it is I'm looking to display is the week number value as a column header.

Is this possible...although I'm tempted to just grab the data and display properly through code/(asp.net) but I was wondering if there was away to display it like this in SQL?

Does anyone know or think that that this might be the best way?

Please vote for dynamic pivot for the next version of SQL Server (not SQL 2012)! http://connect.microsoft.com/SQLServer/feedback/details/127071/pivot — Aaron Bertrand, Mar 06 '12 at 17:42

score 7 · Accepted Answer · answered Mar 06 '12 at 14:35

There are two ways of doing this with static SQL and dynamic SQL:

Static Pivot:

SELECT P.ItemNo, IsNull(P.[1], 0) as Wk1, IsNull(P.[2], 0) as Wk2
    , IsNull(P.[3], 0) as Wk3, IsNull(P.[4], 0) as Wk4
    , IsNull(P.[5], 0) as Wk5, IsNull(P.[6], 0) as Wk6
    , IsNull(P.[7], 0) as Wk7, IsNull(P.[8], 0) as Wk8
    , IsNull(P.[9], 0) as Wk9
FROM
(
    SELECT ItemNo, WeekNo, [Value]
    FROM dbo.Items
) I
PIVOT
(
    SUM([Value])
    FOR WeekNo IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
) as P

Dynamic Pivot:

DECLARE
@cols AS NVARCHAR(MAX),
@y    AS INT,
@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(w) AS [text()]
FROM (SELECT DISTINCT WeekNo AS W FROM dbo.Items) AS W
ORDER BY W
FOR XML PATH('')),
1, 1, N'');


-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
            FROM (SELECT ItemNo, WeekNo, Value
                    FROM dbo.Items) AS I
            PIVOT(SUM(Value) FOR WeekNo IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;
GO

@bluefeet Is there a benefit to use `NVARCHAR` vs `VARCHAR` ? — Kermit, Aug 28 '12 at 20:04
@njk see [this answer](http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar) — Taryn, Aug 28 '12 at 21:13
@bluefeet Thanks for that. My main concern is with performance as discussed [here](http://stackoverflow.com/questions/35366/varchar-vs-nvarchar-performance) and [here](http://blog.brianhartsock.com/2008/12/14/nvarchar-vs-varchar-in-sql-server-beware/). What are your thoughts on this? — Kermit, Aug 28 '12 at 21:56
@njk it depends on the situation but for dynamic SQL I always use nvarchar — Taryn, Aug 28 '12 at 22:36

Arion · Answer 2 · 2012-03-06T14:39:00.677

Maybe something like this:

Test data

CREATE TABLE #tbl 
    (
        ITEMNO VARCHAR(100),
        WEEKNO INT, 
        VALUE FLOAT
    )

INSERT INTO #tbl
VALUES
    ('A1234',1,805),
    ('A2345',2,14.50),  
    ('A3547',2,1396.70),  
    ('A2208',1,17.65),
    ('A4326',6,19.99)

Week columns

DECLARE @cols VARCHAR(MAX)

;WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 52 )
SELECT @cols = COALESCE(@cols + ','+QUOTENAME('WK'+CAST(n AS VARCHAR(2))),
                     QUOTENAME('WK'+CAST(n AS VARCHAR(2))))
FROM
    Nbrs

Just the included weeks

DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY WEEKNO ORDER BY WEEKNO) AS RowNbr,
        WEEKNO
    FROM
        #tbl
)
SELECT @cols = COALESCE(@cols + ','+QUOTENAME('WK'+CAST(WEEKNO AS VARCHAR(2))),
                     QUOTENAME('WK'+CAST(WEEKNO AS VARCHAR(2))))
FROM
    CTE
WHERE
    CTE.RowNbr=1

Dynamic pivot

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl.ITEMNO,
        ''WK''+CAST(tbl.WEEKNO AS VARCHAR(2)) AS WEEKNO,
        tbl.VALUE
    FROM
        #tbl as tbl
) AS p
PIVOT
(
    SUM(VALUE)
    FOR WEEKNO IN ('+@cols+')
) AS pvt'
EXECUTE(@query)

Drop the temp table

DROP TABLE #tbl

score 1 · Answer 3 · answered Mar 06 '12 at 14:01

1

Use Pivot, although quite a bit of code.. If you create report in reporting services, can use matrix..

answered Mar 06 '12 at 14:01

Jānis

2,216
1
17
27

score 1 · Answer 4 · answered Mar 06 '12 at 14:01

1

Follow the below walkthrogh which explains it clearly

http://www.tsqltutorials.com/pivot.php

answered Mar 06 '12 at 14:01

PraveenVenu

8,217
4
30
39

score 1 · Answer 5 · answered Mar 06 '12 at 14:01

1

You can use PIVOT if you want to do this in sql directly.

It can be more efficient to use the SQL Server to do this as opposed to the client depending upon the size of the data and the aggregation.

answered Mar 06 '12 at 14:01

Cade Roux

88,164
40
182
265

SQL Server Pivots: Displaying row values to column headers

5 Answers5

Linked