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.
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
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 ,  AS [3 Years Old] ,  AS [4 Years Old] ,  AS [5 Years Old] FROM dbo.AverageFishLength PIVOT( SUM(AvgLength) FOR FishAge_Years IN ( , ,  ) ) AS PivotTbl
And the result set now looks like this:
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 ,  AS [3 Years Old] ,  AS [4 Years Old] ,  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 , , and  fields to make it more obvious what data is being displayed.
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 ( , ,  ) ) 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
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:
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
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!
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.