Lesson 32 of 54 16 minAdvanced

PIVOT and UNPIVOT

Turn rows into columns for a cross-tab report and back again: the PIVOT operator and its hidden grouping, conditional aggregation as the flexible alternative, building a dynamic PIVOT safely, and UNPIVOT with its NULL behaviour.

Queries return long data: one row per fact. People often want wide data, a cross-tab with one row per thing and one column per period. Here is the sales value of each category per month, the long way:

Query.sql

1SELECT c.Name AS Category,2       LEFT(DATENAME(month, o.OrderDate), 3) AS Mon,3       SUM(oi.Quantity * oi.UnitPrice) AS Value4FROM dbo.Orders AS o5JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId6JOIN dbo.Products   AS p  ON p.ProductId = oi.ProductId7JOIN dbo.Categories AS c  ON c.CategoryId = p.CategoryId8WHERE o.Status <> 'Cancelled'9GROUP BY c.Name, LEFT(DATENAME(month, o.OrderDate), 3), MONTH(o.OrderDate)10ORDER BY c.Name, MONTH(o.OrderDate);

Output

Category  Mon   Value
--------  ---  ------
Building  Feb  113.00
Building  May  140.00
Fixings   Mar   23.80
Fixings   May   59.50
Fixings   Jun   18.00
Paint     Apr  120.50
Timber    Mar  524.00
Timber    May   84.00
Timber    Jun   81.60
Tools     Feb   38.00
Tools     Mar   89.00
Tools     May   18.50
Tools     Jun    9.75

The PIVOT operator

PIVOT turns the values of one column (Mon) into column headings and puts an aggregate in each cell:

Query.sql

1SELECT Category, [Feb], [Mar], [Apr], [May], [Jun]2FROM (3    SELECT c.Name AS Category,4           LEFT(DATENAME(month, o.OrderDate), 3) AS Mon,5           oi.Quantity * oi.UnitPrice AS Value6    FROM dbo.Orders AS o7    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId8    JOIN dbo.Products   AS p  ON p.ProductId = oi.ProductId9    JOIN dbo.Categories AS c  ON c.CategoryId = p.CategoryId10    WHERE o.Status <> 'Cancelled'11) AS src12PIVOT (SUM(Value) FOR Mon IN ([Feb], [Mar], [Apr], [May], [Jun])) AS pv13ORDER BY Category;

Output

Category     Feb     Mar     Apr     May    Jun
--------  ------  ------  ------  ------  -----
Building  113.00    NULL    NULL  140.00   NULL
Fixings     NULL   23.80    NULL   59.50  18.00
Paint       NULL    NULL  120.50    NULL   NULL
Timber      NULL  524.00    NULL   84.00  81.60
Tools      38.00   89.00    NULL   18.50   9.75

Read the PIVOT clause as: “sum Value, spreading it for each Mon in these columns”. The pieces are:

the source: a derived table (or CTE) with exactly the columns PIVOT needs;
the aggregate: SUM(Value), which fills each cell;
the spreading column: FOR Mon, whose values become headings;
the column list: IN ([Feb], …), typed out in full. Values not in the list are ignored.

Every other column of the source (here only Category) is a grouping column: PIVOT makes one row per different value. A cell with no rows is NULL, not 0.

Interactive

Rows into columns

Leave OrderId in the source Empty cells as 0 (conditional aggregation)

SELECT Category, [Feb], [Mar], [Apr], [May], [Jun]
FROM (SELECT Category, Mon, Value FROM src) AS s
PIVOT (SUM(Value) FOR Mon IN ([Feb], [Mar], [Apr], [May], [Jun])) AS pv;

Category	Feb	Mar	Apr	May	Jun
Building
Fixings
Paint
Timber
Tools

	OrderId	Category	Mon	Value
1	2	Building	Feb	72.00
2	2	Building	Feb	41.00
3	7	Building	May	140.00
4	3	Fixings	Mar	23.80
5	7	Fixings	May	59.50
6	9	Fixings	Jun	18.00
7	5	Paint	Apr	108.00
8	5	Paint	Apr	12.50
9	4	Timber	Mar	272.00
10	4	Timber	Mar	252.00
11	8	Timber	May	84.00
12	10	Timber	Jun	81.60
13	1	Tools	Feb	18.50
14	1	Tools	Feb	19.50
15	3	Tools	Mar	89.00
16	8	Tools	May	18.50
17	10	Tools	Jun	9.75

Building in Feb: 2 source rows (72.00, 41.00), highlighted below. SUM turns them into the one value in the cell. Category is the only other column, so there is one row per category.

The hidden grouping trap

PIVOT never says which columns it groups by: it groups by all the source columns it was not told about. Select the source straight from a table, or add one innocent column, and the rows no longer merge:

Query.sql

1SELECT Category, [Feb], [Mar], [Apr], [May], [Jun]2FROM (3    SELECT c.Name AS Category, o.OrderId,      -- one extra column4           LEFT(DATENAME(month, o.OrderDate), 3) AS Mon,5           oi.Quantity * oi.UnitPrice AS Value6    FROM dbo.Orders AS o7    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId8    JOIN dbo.Products   AS p  ON p.ProductId = oi.ProductId9    JOIN dbo.Categories AS c  ON c.CategoryId = p.CategoryId10    WHERE o.Status <> 'Cancelled'11) AS src12PIVOT (SUM(Value) FOR Mon IN ([Feb], [Mar], [Apr], [May], [Jun])) AS pv13ORDER BY Category;

Output

Category     Feb     Mar     Apr     May    Jun
--------  ------  ------  ------  ------  -----
Building  113.00    NULL    NULL    NULL   NULL
Building    NULL    NULL    NULL  140.00   NULL
Fixings     NULL   23.80    NULL    NULL   NULL
Fixings     NULL    NULL    NULL   59.50   NULL
Fixings     NULL    NULL    NULL    NULL  18.00
Paint       NULL    NULL  120.50    NULL   NULL
Timber      NULL  524.00    NULL    NULL   NULL
Timber      NULL    NULL    NULL   84.00   NULL
Timber      NULL    NULL    NULL    NULL  81.60
Tools      38.00    NULL    NULL    NULL   NULL
Tools       NULL   89.00    NULL    NULL   NULL
Tools       NULL    NULL    NULL   18.50   NULL
Tools       NULL    NULL    NULL    NULL   9.75

Conditional aggregation: the flexible alternative

The same cross-tab can be written with GROUP BY and one CASE per column. It is longer, but the grouping is visible, each column can have its own aggregate or default, and totals are easy:

Query.sql

1SELECT c.Name AS Category,2       SUM(CASE WHEN MONTH(o.OrderDate) = 2 THEN oi.Quantity * oi.UnitPrice ELSE 0 END) AS Feb,3       SUM(CASE WHEN MONTH(o.OrderDate) = 3 THEN oi.Quantity * oi.UnitPrice ELSE 0 END) AS Mar,4       SUM(CASE WHEN MONTH(o.OrderDate) = 4 THEN oi.Quantity * oi.UnitPrice ELSE 0 END) AS Apr,5       SUM(CASE WHEN MONTH(o.OrderDate) = 5 THEN oi.Quantity * oi.UnitPrice ELSE 0 END) AS May,6       SUM(CASE WHEN MONTH(o.OrderDate) = 6 THEN oi.Quantity * oi.UnitPrice ELSE 0 END) AS Jun,7       SUM(oi.Quantity * oi.UnitPrice) AS Total8FROM dbo.Orders AS o9JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId10JOIN dbo.Products   AS p  ON p.ProductId = oi.ProductId11JOIN dbo.Categories AS c  ON c.CategoryId = p.CategoryId12WHERE o.Status <> 'Cancelled'13GROUP BY c.Name14ORDER BY c.Name;

Output

Category     Feb     Mar     Apr     May    Jun   Total
--------  ------  ------  ------  ------  -----  ------
Building  113.00    0.00    0.00  140.00   0.00  253.00
Fixings     0.00   23.80    0.00   59.50  18.00  101.30
Paint       0.00    0.00  120.50    0.00   0.00  120.50
Timber      0.00  524.00    0.00   84.00  81.60  689.60
Tools      38.00   89.00    0.00   18.50   9.75  155.25

ELSE 0 turns the empty cells into 0. Leave it out and they are NULL, as with PIVOT. Counting works the same way: COUNT(CASE WHEN … THEN 1 END) counts the rows where the condition holds, because COUNT skips the NULLs the CASE returns otherwise.

A dynamic PIVOT

PIVOT needs its column list written in the query. If the columns depend on the data (every month that has orders, say), build the query as text and run it with sp_executesql. QUOTENAME puts each value in square brackets so that a strange value cannot break, or inject into, the SQL:

Query.sql

1DECLARE @cols nvarchar(max), @query nvarchar(max);2 3SELECT @cols = STRING_AGG(QUOTENAME(Status), N', ') WITHIN GROUP (ORDER BY Status)4FROM (SELECT DISTINCT Status FROM dbo.Orders) AS s;5 6SET @query = N'7SELECT CustomerId, ' + @cols + N'8FROM (SELECT CustomerId, Status, OrderId FROM dbo.Orders) AS src9PIVOT (COUNT(OrderId) FOR Status IN (' + @cols + N')) AS pv10ORDER BY CustomerId;';11 12PRINT @cols;13EXEC sp_executesql @query;

Output

[Cancelled], [Pending], [Shipped]

CustomerId  Cancelled  Pending  Shipped
----------  ---------  -------  -------
         1          0        0        2
         2          0        1        1
         3          1        0        0
         4          0        0        2
         6          0        0        2
         8          0        1        0

UNPIVOT: columns back into rows

UNPIVOT does the reverse: it turns a set of columns into two columns, one holding the old column name and one the value. A stocktake sheet arrives wide, one column per product:

Query.sql

1CREATE TABLE #Stocktake (Shop nvarchar(20), Hammers int, Drills int, Tapes int);2INSERT INTO #Stocktake VALUES3    (N'Leeds', 12, 3, 20),4    (N'York',   8, NULL, 15);5 6SELECT Shop, Product, Qty7FROM #Stocktake8UNPIVOT (Qty FOR Product IN (Hammers, Drills, Tapes)) AS u9ORDER BY Shop, Product;

Output

Shop   Product  Qty
-----  -------  ---
Leeds  Drills     3
Leeds  Hammers   12
Leeds  Tapes     20
York   Hammers    8
York   Tapes     15

Six cells went in, five rows came out: UNPIVOT drops NULLs. York’s drill count is missing, and so is the row. If you need it, use CROSS APPLY with a VALUES list instead, which keeps every cell. APPLY is the next lesson:

Query.sql

1CREATE TABLE #Stocktake (Shop nvarchar(20), Hammers int, Drills int, Tapes int);2INSERT INTO #Stocktake VALUES3    (N'Leeds', 12, 3, 20),4    (N'York',   8, NULL, 15);5 6SELECT s.Shop, v.Product, v.Qty7FROM #Stocktake AS s8CROSS APPLY (VALUES (N'Hammers', s.Hammers),9                    (N'Drills',  s.Drills),10                    (N'Tapes',   s.Tapes)) AS v(Product, Qty)11ORDER BY s.Shop, v.Product;

Output

Shop   Product   Qty
-----  -------  ----
Leeds  Drills      3
Leeds  Hammers    12
Leeds  Tapes      20
York   Drills   NULL
York   Hammers     8
York   Tapes      15

Quick check

A PIVOT source has the columns Region, Year, Quarter and Sales, and the PIVOT is SUM(Sales) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4]). One row per what?

Quick check

What happens to a NULL cell when you UNPIVOT?

Practice

Using conditional aggregation, show one row per order status with three columns counting that status’s orders in Q1 (Jan–Mar), Q2 (Apr–Jun) and in total. Sort by status.

Show a hint

GROUP BY Status, and COUNT(CASE WHEN MONTH(OrderDate) <= 3 THEN 1 END) for Q1. The NULL warning is expected: it comes from the CASEs that return NULL.

Show the solution

Solution.sql

1SELECT Status,2       COUNT(CASE WHEN MONTH(OrderDate) BETWEEN 1 AND 3 THEN 1 END) AS Q1,3       COUNT(CASE WHEN MONTH(OrderDate) BETWEEN 4 AND 6 THEN 1 END) AS Q2,4       COUNT(*) AS Total5FROM dbo.Orders6GROUP BY Status7ORDER BY Status;

Output

Warning: Null value is eliminated by an aggregate or other SET operation.

Status     Q1  Q2  Total
---------  --  --  -----
Cancelled   0   1      1
Pending     0   2      2
Shipped     4   3      7

Remember

PIVOT turns the values of one column into columns, with an aggregate in each cell.
The column list in IN (…) must be typed out; values not listed are ignored.
PIVOT groups by every source column it is not told about: feed it from a narrow derived table.
Empty cells are NULL with SUM (0 with COUNT).
Conditional aggregation (SUM(CASE …) with GROUP BY) does the same, with visible grouping and easy totals.
A dynamic PIVOT builds the column list with STRING_AGG(QUOTENAME(…)) and runs it with sp_executesql.
UNPIVOT turns columns into rows and drops NULLs; CROSS APPLY (VALUES …) keeps them.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

PIVOT and UNPIVOT

The PIVOT operator

Rows into columns

The hidden grouping trap

Conditional aggregation: the flexible alternative

A dynamic PIVOT

UNPIVOT: columns back into rows

Remember