Home > Microsoft, SQL Server > T-SQL Pivot and UnPivot… What?

T-SQL Pivot and UnPivot… What?


Ok, put away the Excel.  Why am I mentioning Excel?  Hmmmm… maybe you should learn a bit about Pivot tables.  From a user perspective they are kind of important.  Sales reports are often targets for this treatment where you might have a region list that goes down and sales by quarter going across the top.  You all have seen reports like that.  Well, Pivot and Unpivot operators do that right within the sql results.

A note about performance.  Here’s a good blog about CASE versus PIVOT.   While it’s referencing 2005 not 2008 versions, the reasoning stays the same.  But if you are set and determined on using PIVOT with multiple columns, the following will help…

Building the Pivot

First take a simple query using Northwind

SELECT     Orders.OrderID, Orders.OrderDate, [Order Details].UnitPrice, [Order Details].Quantity, Orders.ShipCity
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID

You get:

image

You’ve got the Order ID, OrderDate, Price, Qty and where it went.  The request though is for the total number of orders by city broken out by year with dollar totals…  Sure, you can do that…

Now you’ve changed your query to:

SELECT    
    Count(*) as OrderCount
    , Year(Orders.OrderDate) as OrderYear
    , Sum([Order Details].UnitPrice *[Order Details].Quantity) as OrderTotal
    , Orders.ShipCity
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Year(Orders.OrderDate)
    , Orders.ShipCity 

And you get:

image

Hmmmm…  Maybe some additional sorting before you hand off the result.

SELECT    
    Count(*) as OrderCount
    , Year(Orders.OrderDate) as OrderYear
    , Sum([Order Details].UnitPrice *[Order Details].Quantity) as OrderTotal
    , Orders.ShipCity
FROM         Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Year(Orders.OrderDate)
    , Orders.ShipCity      
Order By ShipCity, OrderYear             

Returns

image

Ok, it’s ready now.  Just hand it off to the business analyst who then runs it into Excel as a data sheet and constructs a pivot table/chart off the data.

Depending on the environment, who’s in charge of the “truth” behind the data.  Sure, this could be good enough.  Really.  I mean, “You can’t handle the truth”, right?

For this purpose, we’re going to take the data into a pivot table and deliver it up that way.  Maybe our customers don’ t know about pivot tables.  Maybe they do and don’t have the time to mess around with Excel.  Whatever the reason, you’re the pivot table provider.

Now, getting the two columns in PIVOT seem to be a bit problematical.  So, I worked around it by first creating a sales pivot table:

SELECT
    ShipCity    
    , [1996] as Y1
    , [1997] as Y2
    , [1998] as Y3

    From
    (SELECT    
            Orders.ShipCity
            , YEAR(Orders.OrderDate) AS OrderYear
            , [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
         FROM          Orders INNER JOIN
                             [Order Details] ON Orders.OrderID = [Order Details].OrderID
    ) AS PVT
    PIVOT
    (
    Sum(OrderTotal) FOR OrderYear IN([1996],[1997],[1998])
    ) as Child
Order By ShipCity

Which Returned:

image

Wait…

What’s missing?

Order Counts are gone.  Well, try as I might, the support for multi columns doesn’t seem to exist and is a pretty common complaint.

So I took a little trip down MSDN way and looking at the documentation…

image

Yup…  Only one group pivoted.

Ok, so just for fun, combine the above example with CTEs, etc for this:

With pvtConcat
AS
(
    SELECT
        ShipCity    
        , [1996] as C1
        , [1997] as C2
        , [1998] as C3

        From
        (SELECT    
                ShipCity
                , YEAR(OrderDate) AS OrderYear
                –, [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
                , Count(OrderID) as OrderCount
             FROM          Orders
             Group By ShipCity, YEAR(OrderDate)
        ) AS PVT
        PIVOT
        (
        Sum(OrderCount) FOR OrderYear IN([1996],[1997],[1998])
        ) as Child
    )

Select ShipCity, C1, C2, C3
into #_concat
from pvtConcat;

With pvtMerge
AS
    (
        SELECT
            ShipCity    
            , [1996] as Y1
            , [1997] as Y2
            , [1998] as Y3

            From
            (SELECT    
                    Orders.ShipCity
                    , YEAR(Orders.OrderDate) AS OrderYear
                    , [Order Details].UnitPrice * [Order Details].Quantity AS OrderTotal
                 FROM          Orders INNER JOIN
                                     [Order Details] ON Orders.OrderID = [Order Details].OrderID
            ) AS PVT
            PIVOT
            (
            Sum(OrderTotal) FOR OrderYear IN([1996],[1997],[1998])
            ) as Child
    )
select * into #_merge from pvtMerge;

Select cc.ShipCity, cc.C1, cc.C2, cc.C3,  pm.Y1, pm.Y2, pm.Y3  from #_concat cc Inner Join #_merge pm on cc.ShipCity = pm.ShipCity

You get:

image

Now wasn’t that the cat’s MEOW…

Mentioned was unpivot.  Works in a similar fashion and the syntax is (from SQL Books Online):

–Unpivot the table.

SELECT VendorID, Employee, Orders

FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p

UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt;

Advertisements
Categories: Microsoft, SQL Server Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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: