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;

Published by

Andre

Originally, this was a pretty darn boring post. Kinda like... Well I won't go there. Perhaps its still on the robotic side but... I could say I like music. Safe, generic and non-comittal. Or, I could say that I've been having a blast tuning up my old guitars, getting blisters on my fingers and turning the amp up past 2. Amazaing what a little overdrive and a half pressed wahwah pedal can do for a sound. Get that cool "Money for Nothin" vibe happening. I get a real kick out of reading old Sci Fi. Reading Asimov's vision about the future is really entertaining now. When he wrote much of the material, the items that were futuristic were day to day tools I used in the early part of my career. Microfiche and the like. I also remember that upstart Microsoft and MS DOS...and can you say Lotus 123? So maybe this is a little better than "I like to read and play music". My career is focused on Team Leadership and Technology. Both share the attributes of continual growth and education. Currently, I manage a team of 4 programmers as direct reports. I've been in this role since 08/2007. Prior to that, I was the team lead (on site) for integration with the customer. Customers ranged from local government to manufacturing and medical. Teams ranged in size from one to six additional team members. On the other side is technology. I've been keeping current with .net technologies focusing on C# and Sharepoint (2007/2010). Specialties Team Building and Management Technical Staff Recruiting Microsoft Visual Studio 7 through 2010 (VB.NET and C#.NET) Microsoft SQL Server 6.5-2008R2 (DTS, TSQL, SSIS, SSAS, and SSRS ) SharePoint 2010 (Office Integration, InfoPath, Site Management and planning) Windows Server 2008 R2 AD DS PowerShell Techology analysis Puchasng and working with vendors Microsoft licensing management and compliance Business Systems Analysis Traning plans Mentoring Training coordination.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s