The SQL Server Pivot Function

Many fisherman, after catching a noteworthy fish, will take a quick picture before letting it go. A picture serves as proof of the catch, as well as something to look back on and remember all the fun that was had on the water. Here’s a fair sized northern pike I caught on Lake Okauchee this last fall.

2014 Fall Northern Pike

There are a couple of ways a fisherman can make sure the picture does justice to the true size of the fish (meaning make it look bigger ūüėČ). Since a camera only has a single point of view, if you hold the fish out toward the camera and try to keep your hands behind the fish, it’ll look bigger since it’s closer to the camera. Another way is to widen the view and lower the angle of the camera. Changing the way the camera views the fisherman and fish can give a different perspective that enhances your claim on being a good fisherman. In SQL Server, the PIVOT function can be used to change the way data is presented, and make it more usable to those that need the results.

Let’s take a look at a simple example of the PIVOT function. First we’ll create a very basic table, the average length of a fish after X years of life:

CREATE TABLE dbo.AverageFishLength
    (
      Fishtype VARCHAR(50) ,
      AvgLength DECIMAL(8, 2) ,
      FishAge_Years INT
    )
INSERT  INTO dbo.AverageFishLength
        ( Fishtype, AvgLength, FishAge_Years )
VALUES  ( 'Muskie', 32.75, 3 ),
        ( 'Muskie', 37.5, 4 ),
        ( 'Muskie', 39.75, 5 ),
        ( 'Walleye', 16.5, 3 ),
        ( 'Walleye', 18.25, 4 ),
        ( 'Walleye', 20.0, 5 ),
        ( 'Northern Pike', 20.75, 3 ),
        ( 'Northern Pike', 23.25, 4 ),
        ( 'Northern Pike', 26.0, 5 )

A simple SELECT from this table will yield the following results:

SELECT  *
FROM    dbo.AverageFishLength

20150107 PIVOT Simple Original Select

All the data is there, but it’s not really set up in a way that makes it easy to compare. ¬†If I want to look at how the length between the three fish compare when they are four years old, I have to look first at the top, then the middle, then the bottom of the results. ¬†Imagine how much more difficult this would be with a more complex (real world) example. ¬†So let’s use the PIVOT function to see if we can make this a little easier to interpret:

SELECT  Fishtype ,
        [3] AS [3 Years Old] ,
        [4] AS [4 Years Old] ,
        [5] AS [5 Years Old]
FROM    dbo.AverageFishLength  PIVOT( SUM(AvgLength) 
                               FOR FishAge_Years IN ( [3], [4], [5] ) ) AS PivotTbl

And the result set now looks like this:

20150107 PIVOT Simple Pivot Select

It’s now quite easy to look down the columns and see that, at four years old, the Muskie is about 14 inches longer than the Northern Pike and almost 20 inches longer than the Walleye. ¬†Let’s take a look at the syntax of the PIVOT function because, in my opinion, it’s somewhat confusing.

SELECT  Fishtype ,
        [3] AS [3 Years Old] ,
        [4] AS [4 Years Old] ,
        [5] AS [5 Years Old]
FROM    dbo.AverageFishLength

The first part is SELECTing the fields.  You can select any of the non-pivoted fields (in this case, Fishtype).  Then you can select any or all of the fields that you are pivoting on.  In this example I have aliased the [3], [4], and [5] fields to make it more obvious what data is being displayed.

PIVOT( SUM(AvgLength)

The next part is the pivot function and the aggregate function. ¬†You always have to include an aggregate function, even if you don’t need to see it in your result set.

FOR FishAge_Years IN ( [3], [4], [5] ) ) AS PivotTbl

Lastly, you include the field whose values you want to be the new columns, and include each value that you want. You also have to create an alias for the pivot sub-table.

Now that we get the syntax and have looked at a basic example, let’s create something a little more complex. ¬†I’ll build a table to record notable fish catches over the last few years:

CREATE TABLE dbo.FishCaught (CatchDateTime DATETIME2, FishType VARCHAR(25), Length_Inches DECIMAL(8,2), Weight_LBs DECIMAL (8,2))
INSERT INTO dbo.FishCaught
        ( CatchDateTime ,
          FishType ,
          Length_Inches ,
          Weight_LBs
        )
VALUES  ( '2010-05-28 06:35:00','Muskie',37,10.5),
       ( '2010-06-06 16:58:00','Walleye',19.25,2.33),
       ( '2010-06-07 06:42:00','Smallmouth Bass',17,3.5),
       ( '2010-06-29 18:12:00','Muskie',34.25,5.75),
       ( '2010-08-22 14:12:00','Largemouth Bass',15,1.75),
       ( '2010-09-01 10:09:00','Walleye',24.25,6.25),
       ( '2010-10-25 13:46:00','Muskie',46,19.75),
       ( '2011-06-28 15:15:00','Crappie',12,1.75),
       ( '2011-06-03 18:43:00','Yellow Perch',14,2),
       ( '2011-07-16 14:28:00','Muskie',41.25,11.5),
       ( '2011-07-22 08:38:00','Smallmouth Bass',19.25,6.5),
       ( '2011-08-09 11:31:00','Muskie',36.75,9.75),
       ( '2011-09-14 06:29:00','Walleye',19.75,3.75),
       ( '2011-11-24 08:49:00','Largemouth Bass',20.5,8.5),
       ( '2012-08-10 20:11:00','Muskie',43.25,13),
       ( '2012-08-13 15:55:00','Lake Trout',35,10.25),
       ( '2012-10-29 08:04:00','Smallmouth Bass',17,10.5),
       ( '2012-10-30 11:08:00','Gar',26,2.5),
       ( '2013-09-05 17:54:00','Muskie',38.25,9.75),
       ( '2013-10-04 07:17:00','Northern Pike',26.75,10.5),
       ( '2013-10-28 08:47:00','Muskie',48.5,23.25),
       ( '2014-06-06 16:26:00','Walleye',22.25,5.75),
       ( '2014-06-27 06:39:00','Muskie',36.75,8.25),
       ( '2014-11-20 11:33:00','Muskie',45.5,14.75)

Let’s say we want to look at the length of all the fish we’ve caught with the fish name being the columns and the lengths. If we try our previous syntax, the results are not going to be what we expect:

SELECT  Muskie ,
        Walleye ,
        [Smallmouth Bass] ,
        [Largemouth Bass] ,
        Crappie ,
        [Yellow Perch] ,
        [Lake Trout] ,
        Gar ,
        [Northern Pike]
FROM    dbo.FishCaught 
PIVOT ( SUM(Length_Inches) FOR FishType IN ( Muskie,
                                             Walleye,
                                             [Smallmouth Bass],
                                             [Largemouth Bass],
                                             Crappie,
                                             [Yellow Perch],
                                             [Lake Trout],
                                             Gar,
                                             [Northern Pike] ) ) AS PivotTbl

20150107 PIVOT Complex Pivot bad results

This¬†occurs because the pivot function always works against all of the fields in the table being pivoted. ¬†In this case, it’s including the CatchDateTime and the Weight_LBs. ¬†We can rewrite the query to show exactly what SQL is doing by putting it back into CASE statements (PIVOT is a syntactic shorthand for these case statements with a group by and aggregate):

SELECT  SUM(CASE WHEN FishType = 'muskie' THEN Length_Inches
                 ELSE NULL
            END) AS Muskie ,
        SUM(CASE WHEN FishType = 'Walleye' THEN Length_Inches
                 ELSE NULL
            END) AS Walleye ,
        SUM(CASE WHEN FishType = 'Smallmouth Bass' THEN Length_Inches
                 ELSE NULL
            END) AS [Smallmouth Bass] ,
        SUM(CASE WHEN FishType = 'Largemouth Bass' THEN Length_Inches
                 ELSE NULL
            END) AS [Largemouth Bass] ,
        SUM(CASE WHEN FishType = 'Crappie' THEN Length_Inches
                 ELSE NULL
            END) AS Crappie ,
        SUM(CASE WHEN FishType = 'Yellow Perch' THEN Length_Inches
                 ELSE NULL
            END) AS [Yellow Perch] ,
        SUM(CASE WHEN FishType = 'Lake Trout' THEN Length_Inches
                 ELSE NULL
            END) AS [Lake Trout] ,
        SUM(CASE WHEN FishType = 'Gar' THEN Length_Inches
                 ELSE NULL
            END) AS Gar ,
        SUM(CASE WHEN FishType = 'Northern Pike' THEN Length_Inches
                 ELSE NULL
            END) AS [Northern Pike]
FROM    dbo.FishCaught
GROUP BY FishType ,
        CatchDateTime ,
        Weight_LBs;

So if we want to get nice results like last time, we have to use a subselect or common table expression (cte) to just pivot on the fields we need. ¬†We’ll add a row number field using a windowing function to make sure that we include all results, not just get the aggregate (MAX in this case) of each fish type:

SELECT  muskie ,
        walleye ,
        [Smallmouth Bass],
        [Largemouth Bass],
        Crappie,
        [Yellow Perch],
        [Lake Trout],
        Gar,
        [Northern Pike]
FROM    ( SELECT    FishType ,
                    Length_Inches ,
                    ROW_NUMBER() OVER ( PARTITION BY FishType ORDER BY Length_Inches ) rn
          FROM      dbo.FishCaught
        ) d PIVOT( MAX(Length_Inches) FOR FishType IN ( muskie,
                                                        walleye,
                                                       [Smallmouth Bass],
                                                        [Largemouth Bass],
                                                        Crappie,
                                                        [Yellow Perch],
                                                        [Lake Trout],
                                                        Gar,
                                                        [Northern Pike] ) ) AS FishCaughtPivot;

Now our results look usable:

20150107 PIVOT Complex Pivot good results

If we hadn’t included the ROW_NUMBER() field, we would only have gotten the longest length for each fish:

SELECT  muskie ,
        walleye ,
        [Smallmouth Bass],
        [Largemouth Bass],
        Crappie,
        [Yellow Perch],
        [Lake Trout],
        Gar,
        [Northern Pike]
FROM    ( SELECT    FishType ,
                    Length_Inches ,
          FROM      dbo.FishCaught
        ) d PIVOT( MAX(Length_Inches) FOR FishType IN ( muskie,
                                                        walleye,
                                                       [Smallmouth Bass],
                                                        [Largemouth Bass],
                                                        Crappie,
                                                        [Yellow Perch],
                                                        [Lake Trout],
                                                        Gar,
                                                        [Northern Pike] ) ) AS FishCaughtPivot

Result:

20150107 PIVOT Complex Pivot aggregate

Let’s consider one more example that makes this concept more interesting. ¬†Let’s say I want to see what month I caught the most of each type of fish, per pound. ¬†I can use the PIVOT function to aggregate the Weight_LBs field across the different months with this query:

SELECT  *
FROM    ( SELECT    DATENAME(MONTH, CatchDateTime) AS MonthNam ,
                    FishType ,
                    Weight_LBs
          FROM      dbo.FishCaught
        ) sq PIVOT ( SUM(Weight_LBs) FOR MonthNam IN ( January, February,
                                                       March, April, May, June,
                                                       July, August, September,
                                                       October, November,
                                                       December ) ) PT

The results let us see how we’ve done each month. October seems to be the time to hook up with some big muskie!
20150107 PIVOT Weight by Month
I hope this post has given you a good idea of how to use the PIVOT function, and times when it may be a good idea.

Advertisements