PIVOT & UNPIVOT
/tldr: Restructuring data by rotating rows to columns and columns to rows.
THE CORE IDEA: Changing Data Orientation
These operators bridge the gap between transactional data (tall and normalized) and reporting data (wide and summarized).
Starting Point (Source Data)
Imagine a simple table of product sales showing sales quantity per quarter:
| Product | Quarter | Quantity |
|---|---|---|
| Laptop | Q1 | 100 |
| Laptop | Q2 | 120 |
| Monitor | Q1 | 50 |
| Monitor | Q2 | 60 |
1. PIVOT: Rows to Columns
**PIVOT** rotates a set of unique row values from one column into multiple new columns. This is typically used for creating summary reports where categories become headers.
The Syntax Structure
SELECT [Grouping Columns], [Pivot Column 1], [Pivot Column 2], ...
FROM (
-- Input Query (The data you want to pivot)
SELECT [Grouping Column], [Pivoting Column], [Value Column]
FROM YourTable
) AS SourceTable
PIVOT (
-- 1. Aggregation: What calculation to perform on the values?
AggregateFunction([Value Column])
-- 2. Pivoting: Which unique values become the new columns?
FOR [Pivoting Column] IN ([Pivot Column 1], [Pivot Column 2], ...)
) AS PivotTable;
PIVOT Example
SELECT Product, Q1_Sales, Q2_Sales
FROM (
SELECT Product, Quarter, Quantity
FROM SalesData
) AS SourceData
PIVOT (
SUM(Quantity) -- Aggregate the Quantity
FOR Quarter IN ([Q1] AS Q1_Sales, [Q2] AS Q2_Sales) -- Pivot the Quarter values
) AS PivotResult;
Result of PIVOT
| Product | Q1_Sales | Q2_Sales |
|---|---|---|
| Laptop | 100 | 120 |
| Monitor | 50 | 60 |
**Key Requirement:** The list of new columns in the IN (...) clause must be explicitly known and listed. You cannot pivot dynamically (though some databases offer workarounds).
2. UNPIVOT: Columns to Rows
**UNPIVOT** performs the reverse operation: it rotates multiple column headers into two new columns—one for the original header name (the Attribute) and one for the value. This is useful for normalizing data for analysis or storage.
The Syntax Structure
SELECT [Grouping Columns], [Attribute Column], [Value Column]
FROM (
-- Input Query (The data you want to unpivot)
SELECT [Grouping Columns], [Pivot Column 1], [Pivot Column 2], ...
FROM YourTable
) AS SourceTable
UNPIVOT (
-- 1. Value Column: Name of the new column that holds the old cell values
[Value Column Name]
-- 2. Attribute Column: Name of the new column that holds the old column headers
FOR [Attribute Column Name] IN ([Pivot Column 1], [Pivot Column 2], ...)
) AS UnpivotTable;
UNPIVOT Example
-- Starting with the PIVOT Result (wide table)
SELECT Product, Quarter, Quantity
FROM WideSalesData
UNPIVOT (
Quantity FOR Quarter IN ([Q1_Sales], [Q2_Sales]) -- Columns to turn into rows
) AS UnpivotResult;
Result of UNPIVOT
| Product | Quarter | Quantity |
|---|---|---|
| Laptop | Q1_Sales | 100 |
| Laptop | Q2_Sales | 120 |
| Monitor | Q1_Sales | 50 |
| Monitor | Q2_Sales | 60 |
**Note:** The old column headers (e.g., Q1\_Sales, Q2\_Sales) become text values in the new "Quarter" column.
PIVOT for Reports, UNPIVOT for Normalization.