The PIVOT Operator

Published Mar 23 2019 05:05 AM 175 Views
Microsoft
First published on MSDN on Jul 03, 2007

In my next few posts, I'm going to look at how SQL Server implements the PIVOT and UNPIVOT operators.  Let's begin with the PIVOT operator.  The PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table are derived from the values in the original table.  For example, suppose we want to store annual sales data by employee.  We might create a schema such as the following:



CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)


Notice that this schema has one row per employee per year .  Moreover, notice that in the sample data employees 2 and 3 only have sales data for two of the three years worth of data.  Now suppose that we'd like to transform this data into a table that has one row per employee with all three years of sales data in each row .  We can achieve this conversion very easily using PIVOT:



SELECT EmpId, [2005], [2006], [2007]
FROM (SELECT EmpId, Yr, Sales FROM Sales) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p


I'm not going to delve into the PIVOT syntax which is already documented in Books Online .  Suffice it to say that this statement sums up the sales for each employee for each of the specified years and outputs one row per employee.  The resulting output is:

EmpId 2005 2006 2007
----------- --------------------- --------------------- ---------------------
1 12000.00 18000.00 25000.00
2 15000.00 6000.00 NULL
3 NULL 20000.00 24000.00

Notice that SQL Server inserts NULLs for the missing sales data for employees 2 and 3.


The SUM keyword (or some other aggregate) is required.  If the Sales table includes multiple rows for a particular employee for a particular year, PIVOT does aggregate them - in this case by summing them -  into a single data point in the result.  Of course, in this example, since the entry in each "cell" of the output table is the result of summing a single input row, we could just as easily have used another aggregate such as MIN or MAX.  I've used SUM since it is more intuitive.


This PIVOT example is reversible.  The information in the output table can be used to reconstruct the original input table using an UNPIVOT operation (which I will cover in a later post).  However, not all PIVOT operations are reversible.  To be reversible, a PIVOT operation must meet the following criteria:



  • All of the input data must be transformed. If we include a filter of any kind including on the IN clause, some data may be omitted from the PIVOT result. For example, if we altered the above example only to output sales for 2006 and 2007, clearly we could not reconstruct the 2005 sales data from the result.

  • Each cell in the output table must derive from a single input row. If multiple input rows are aggregated into a single cell, there is no way to reconstruct the original input rows.

  • The aggregate function must be an identity function (when used on a single input row). SUM, MIN, MAX, and AVG all return the single input value unchanged and, thus, can be reversed. COUNT does not return its input value unchanged and, thus, cannot be reversed.

Here is an example of a non-reversible PIVOT operation.  This example, calculates the total sales for all employees for all three years.  It does not itemize the output by employee.



SELECT [2005], [2006], [2007]
FROM (SELECT Yr, Sales FROM Sales) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p


Here is the output.  Each cell represents the sum of two or three rows from the input table.

2005 2006 2007
--------------------- --------------------- ---------------------
27000.00 44000.00 49000.00

In my next post, I'll look at some example PIVOT query plans.

%3CLINGO-SUB%20id%3D%22lingo-sub-383305%22%20slang%3D%22en-US%22%3EThe%20PIVOT%20Operator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383305%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jul%2003%2C%202007%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EIn%20my%20next%20few%20posts%2C%20I'm%20going%20to%20look%20at%20how%20SQL%20Server%20implements%20the%20PIVOT%20and%20UNPIVOT%20operators.%26nbsp%3B%20Let's%20begin%20with%20the%20PIVOT%20operator.%26nbsp%3B%20The%20PIVOT%20operator%20takes%20a%20normalized%20table%20and%20transforms%20it%20into%20a%20new%20table%20where%20the%20columns%20of%20the%20new%20table%20are%20derived%20from%20the%20values%20in%20the%20original%20table.%26nbsp%3B%20For%20example%2C%20suppose%20we%20want%20to%20store%20annual%20sales%20data%20by%20employee.%26nbsp%3B%20We%20might%20create%20a%20schema%20such%20as%20the%20following%3A%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20Sales%20(EmpId%20INT%2C%20Yr%20INT%2C%20Sales%20MONEY)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(1%2C%202005%2C%2012000)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(1%2C%202006%2C%2018000)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(1%2C%202007%2C%2025000)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(2%2C%202005%2C%2015000)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(2%2C%202006%2C%206000)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(3%2C%202006%2C%2020000)%20%3CBR%20%2F%3E%20INSERT%20Sales%20VALUES(3%2C%202007%2C%2024000)%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3ENotice%20that%20this%20schema%20has%20one%20row%20per%20employee%20%3CB%3E%20per%20year%20%3C%2FB%3E%20.%26nbsp%3B%20Moreover%2C%20notice%20that%20in%20the%20sample%20data%20employees%202%20and%203%20only%20have%20sales%20data%20for%20two%20of%20the%20three%20years%20worth%20of%20data.%26nbsp%3B%20Now%20suppose%20that%20we'd%20like%20to%20transform%20this%20data%20into%20a%20table%20that%20has%20one%20row%20per%20employee%20%3CB%3E%20with%20all%20three%20years%20of%20sales%20data%20in%20each%20row%20%3C%2FB%3E%20.%26nbsp%3B%20We%20can%20achieve%20this%20conversion%20very%20easily%20using%20PIVOT%3A%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3ESELECT%20EmpId%2C%20%5B2005%5D%2C%20%5B2006%5D%2C%20%5B2007%5D%20%3CBR%20%2F%3E%20FROM%20(SELECT%20EmpId%2C%20Yr%2C%20Sales%20FROM%20Sales)%20AS%20s%20%3CBR%20%2F%3E%20PIVOT%20(SUM(Sales)%20FOR%20Yr%20IN%20(%5B2005%5D%2C%20%5B2006%5D%2C%20%5B2007%5D))%20AS%20p%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EI'm%20not%20going%20to%20delve%20into%20the%20PIVOT%20syntax%20which%20is%20already%20documented%20in%20%3CA%20href%3D%22http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Fms177410.aspx%22%20mce_href%3D%22http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Fms177410.aspx%22%20title%3D%22Using%20PIVOT%20and%20UNPIVOT%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Books%20Online%20%3C%2FA%3E%20.%26nbsp%3B%20Suffice%20it%20to%20say%20that%20this%20statement%20sums%20up%20the%20sales%20for%20each%20employee%20for%20each%20of%20the%20specified%20years%20and%20outputs%20one%20row%20per%20employee.%26nbsp%3B%20The%20resulting%20output%20is%3A%3C%2FP%3EEmpId%202005%202006%202007%20%3CBR%20%2F%3E%20-----------%20---------------------%20---------------------%20---------------------%20%3CBR%20%2F%3E%201%2012000.00%2018000.00%2025000.00%20%3CBR%20%2F%3E%202%2015000.00%206000.00%20NULL%20%3CBR%20%2F%3E%203%20NULL%2020000.00%2024000.00%20%3CBR%20%2F%3E%3CP%3ENotice%20that%20SQL%20Server%20inserts%20NULLs%20for%20the%20missing%20sales%20data%20for%20employees%202%20and%203.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20SUM%20keyword%20(or%20some%20other%20aggregate)%20is%20required.%26nbsp%3B%20If%20the%20%3CI%3E%20Sales%20%3C%2FI%3E%20table%20includes%20multiple%20rows%20for%20a%20particular%20employee%20for%20a%20particular%20year%2C%20PIVOT%20does%20aggregate%20them%20-%20in%20this%20case%20by%20summing%20them%20-%26nbsp%3B%20into%20a%20single%20data%20point%20in%20the%20result.%26nbsp%3B%20Of%20course%2C%20in%20this%20example%2C%20since%20the%20entry%20in%20each%20%22cell%22%20of%20the%20output%20table%20is%20the%20result%20of%20summing%20a%20single%20input%20row%2C%20we%20could%20just%20as%20easily%20have%20used%20another%20aggregate%20such%20as%20MIN%20or%20MAX.%26nbsp%3B%20I've%20used%20SUM%20since%20it%20is%20more%20intuitive.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20PIVOT%20example%20is%20reversible.%26nbsp%3B%20The%20information%20in%20the%20output%20table%20can%20be%20used%20to%20reconstruct%20the%20original%20input%20table%20using%20an%20UNPIVOT%20operation%20(which%20I%20will%20cover%20in%20a%20later%20post).%26nbsp%3B%20However%2C%20not%20all%20PIVOT%20operations%20are%20reversible.%26nbsp%3B%20To%20be%20reversible%2C%20a%20PIVOT%20operation%20must%20meet%20the%20following%20criteria%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EAll%20of%20the%20input%20data%20must%20be%20transformed.%20If%20we%20include%20a%20filter%20of%20any%20kind%20including%20on%20the%20IN%20clause%2C%20some%20data%20may%20be%20omitted%20from%20the%20PIVOT%20result.%20For%20example%2C%20if%20we%20altered%20the%20above%20example%20only%20to%20output%20sales%20for%202006%20and%202007%2C%20clearly%20we%20could%20not%20reconstruct%20the%202005%20sales%20data%20from%20the%20result.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EEach%20cell%20in%20the%20output%20table%20must%20derive%20from%20a%20single%20input%20row.%20If%20multiple%20input%20rows%20are%20aggregated%20into%20a%20single%20cell%2C%20there%20is%20no%20way%20to%20reconstruct%20the%20original%20input%20rows.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EThe%20aggregate%20function%20must%20be%20an%20identity%20function%20(when%20used%20on%20a%20single%20input%20row).%20SUM%2C%20MIN%2C%20MAX%2C%20and%20AVG%20all%20return%20the%20single%20input%20value%20unchanged%20and%2C%20thus%2C%20can%20be%20reversed.%20COUNT%20does%20not%20return%20its%20input%20value%20unchanged%20and%2C%20thus%2C%20cannot%20be%20reversed.%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EHere%20is%20an%20example%20of%20a%20non-reversible%20PIVOT%20operation.%26nbsp%3B%20This%20example%2C%20calculates%20the%20total%20sales%20for%20all%20employees%20for%20all%20three%20years.%26nbsp%3B%20It%20does%20not%20itemize%20the%20output%20by%20employee.%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3ESELECT%20%5B2005%5D%2C%20%5B2006%5D%2C%20%5B2007%5D%20%3CBR%20%2F%3E%20FROM%20(SELECT%20Yr%2C%20Sales%20FROM%20Sales)%20AS%20s%20%3CBR%20%2F%3E%20PIVOT%20(SUM(Sales)%20FOR%20Yr%20IN%20(%5B2005%5D%2C%20%5B2006%5D%2C%20%5B2007%5D))%20AS%20p%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EHere%20is%20the%20output.%26nbsp%3B%20Each%20cell%20represents%20the%20sum%20of%20two%20or%20three%20rows%20from%20the%20input%20table.%3C%2FP%3E2005%202006%202007%20%3CBR%20%2F%3E%20---------------------%20---------------------%20---------------------%20%3CBR%20%2F%3E%2027000.00%2044000.00%2049000.00%20%3CBR%20%2F%3E%3CP%3EIn%20my%20next%20post%2C%20I'll%20look%20at%20some%20example%20PIVOT%20query%20plans.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383305%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jul%2003%2C%202007%20In%20my%20next%20few%20posts%2C%20I'm%20going%20to%20look%20at%20how%20SQL%20Server%20implements%20the%20PIVOT%20and%20UNPIVOT%20operators.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383305%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerQueryProcessing%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 05:05 AM
Updated by: