How to switch rows and columns in SQL Server 2008 by pivotting and unpivotting

I came across a scenario in which I needed to switch rows and columns in the results of a query. That’s basically because I need the data to be fed into a chart in one form, and grid in another form.

So here’s how the results looked before I ran my queries.

Before Pivot

And here’s the result after I switched the rows and columns using my queries.

After queries

Now, I haven’t really replaced the column headers with the DateBucket field, but that’s ok in my case, because I already know what the column headers should be based on the parameters I passed to my query.

Firstly, the test data I created is as follows:

CREATE TABLE #test
(
   LineNum bigint,
   DateBucket varchar(8),
   TotalOrders float,
   TotalItems float,
   TotalItem1 float,
   TotalItem2 float,
   TotalItem3 float,
   AverageCost float,
   AveragePrice float,
   AverageProfit float
);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (1, 'Jun 2010', 100.0, 220.0, 53.0, 72.0, 99.0, 1204.0, 2468.0, 1122.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (2, 'Jul 2010', 120.0, 300.0, 54.0, 73.0, 98.0, 1208.0, 2470.0, 1123.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (3, 'Aug 2010', 130.0, 280.0, 55.0, 74.0, 97.0, 1212.0, 2472.0, 1124.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (4, 'Sep 2010', 140.0, 190.0, 56.0, 75.0, 96.0, 1216.0, 2474.0, 1125.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (5, 'Oct 2010', 150.0, 250.0, 57.0, 76.0, 95.0, 1220.0, 2476.0, 1126.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (6, 'Nov 2010', 160.0, 260.0, 58.0, 77.0, 94.0, 1224.0, 2478.0, 1127.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (7, 'Dec 2010', 170.0, 200.0, 59.0, 78.0, 93.0, 1228.0, 2480.0, 1128.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (8, 'Jan 2011', 180.0, 210.0, 60.0, 79.0, 92.0, 1232.0, 2482.0, 1129.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (9, 'Feb 2011', 190.0, 230.0, 61.0, 80.0, 91.0, 1236.0, 2484.0, 1130.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (10, 'Mar 2011', 200.0, 270.0, 62.0, 81.0, 90.0, 1240.0, 2486.0, 1131.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (11, 'Apr 2011', 210.0, 240.0, 63.0, 82.0, 89.0, 1244.0, 2488.0, 1132.0);
INSERT INTO #test(LineNum, DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
VALUES (12, 'May 2011', 220.0, 290.0, 64.0, 83.0, 88.0, 1248.0, 2490.0, 1133.0);
[/code]

Now the first thing I need to do is to Unpivot the data. This will give me a list of DateBucket, ColumnHeaders and Values. Not exactly what I want, but it’ll do for now.  I will store this in a temp table so that I can retrieve it for re-pivotting later. 


CREATE TABLE #TestUnpivot
(
   DateBucket nvarchar(8),
   ItemDescription nvarchar(100),
   ItemValue float
);

INSERT INTO #TestUnpivot(DateBucket, ItemDescription, ItemValue)
SELECT DateBucket,  ItemDescription,  ItemValue
FROM
   (SELECT DateBucket, 
   TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit
   FROM #Test) p
   UNPIVOT
   (ItemValue FOR ItemDescription IN
      (TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit)
   ) AS unpvt

This outputs the following results (There’s more data, but I clipped it for brevity):

After the unpivot

Now I want the ItemDescription field to be data in the rows and I want the months to be columns. So I need to re-pivot the data. To re-pivot the data, I execute the following query:

SELECT ItemDescription as ItemValue,
[1] as Month1, [2] as Month2, [3] as Month3, [4] as Month4, [5] as Month5, [6] as Month6, [7] as Month7, [8] as Month8, [9] as Month9, [10] as Month10, [11] as Month11, [12] as Month12
FROM
   (SELECT v.ItemDescription, v.ItemValue, b.id
    FROM #TestUnpivot v
    join (
    select distinct top 100 PERCENT linenum as id, DateBucket
    from #test
    order by linenum) b on v.DateBucket=b.datebucket
   ) AS SourceTable
   PIVOT
   (
      MIN(ItemValue)
      FOR id IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
   ) AS PivotTable

Note that if you really really wanted to rename the columns, you could do that by using a dynamic query (sp_executesql) but avoid that if you can.Also note that I selected MIN for the aggregate function. This is intentional, because I feel that it requires the least amount of processing. Use of average would require the processing to keep a tally of all the results for later averaging, which I believe would add to the query time, maybe not much, but it would be more than MIN I think.

And finally, the grouping is done via the nested query:

SELECT DISTINCT TOP 100 PERCENT linenum as id, DateBucket
from #test
ORDER BY linenum

This groups the values by the month names found in the DateBucket column.So after executing this query, I get the result I wanted:

After queries

Merging all this into a single query, and I get the following:

SELECT ItemDescription as ItemValue, [1] as Month1, [2] as Month2, [3] as Month3, [4] as Month4, [5] as Month5, [6] as Month6, [7] as  Month7, [8] as Month8, [9] as Month9, [10] as Month10, [11] as Month11, [12] as Month12
FROM
   (SELECT v.ItemDescription, v.ItemValue, b.id
    FROM
    (
       SELECT DateBucket,  ItemDescription,  ItemValue 
       FROM (SELECT DateBucket, TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit 
             FROM #Test) p 
       UNPIVOT 
            (ItemValue FOR ItemDescription IN
                (TotalOrders, TotalItems, TotalItem1, TotalItem2, TotalItem3, AverageCost, AveragePrice, AverageProfit) 
            ) AS unpvt 
    ) as v
    JOIN (
       select distinct top 100 percent linenum as id, DateBucket
       from #Test
       order by linenum) b on v.DateBucket=b.datebucket
    ) AS SourceTable
    PIVOT
    (
       MIN(ItemValue)
       FOR id IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) AS PivotTable

One Response to How to switch rows and columns in SQL Server 2008 by pivotting and unpivotting

  1. Jared Crosby says:

    Awesome article. Had the exact same problem as described here.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: