SQL UNPIVOT: How to Rotate Columns Back into Rows
What Is UNPIVOT?
UNPIVOT is the reverse of PIVOT. Where PIVOT converts rows into columns, UNPIVOT converts columns back into rows — transforming a wide, denormalized table into a tall, normalized one. SQL Server has a native UNPIVOT operator; other databases use UNION ALL or CROSS APPLY (VALUES ...) to achieve the same result.
-- Wide (pivoted) table:
Region | Q1 | Q2 | Q3 | Q4
-------|-------|-------|-------|------
East | 12400 | 15600 | 18200 | 21000
West | 9800 | 11200 | 14500 | 16800
-- After UNPIVOT: normalized
Region | Quarter | Revenue
-------|---------|--------
East | Q1 | 12400
East | Q2 | 15600
...When to Unpivot
- Normalizing spreadsheet imports — spreadsheets often arrive with dates or categories as column headers, which need to become row values for database storage
- Feeding aggregation pipelines — GROUP BY and window functions work on rows, not columns
- Time-series data — month columns become a Month column and a Value column
- ETL transformations — converting report output back into a fact table format
Using the Generator
The source table with the wide format you want to unpivot.
These are the identifier columns that stay as-is — e.g. Region, Product.
List the column names that will become values in a new 'category' column — e.g. Q1, Q2, Q3, Q4.
Give names to the new key column (e.g. Quarter) and value column (e.g. Revenue).
SQL Server uses native UNPIVOT syntax; other databases get a UNION ALL or CROSS APPLY equivalent.
UNPIVOT vs UNION ALL
The native UNPIVOT operator is more concise but only works in SQL Server. The UNION ALL approach works in every database but becomes verbose for many columns:
-- SQL Server UNPIVOT
SELECT Region, Quarter, Revenue
FROM sales_wide
UNPIVOT (Revenue FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])) AS u;
-- UNION ALL (all databases)
SELECT Region, 'Q1' AS Quarter, Q1 AS Revenue FROM sales_wide
UNION ALL
SELECT Region, 'Q2', Q2 FROM sales_wide
UNION ALL
SELECT Region, 'Q3', Q3 FROM sales_wide
UNION ALL
SELECT Region, 'Q4', Q4 FROM sales_wide;Using CROSS APPLY VALUES (SQL Server)
A cleaner alternative that avoids repeating the table name is CROSS APPLY (VALUES ...):
SELECT r.Region, v.Quarter, v.Revenue
FROM sales_wide r
CROSS APPLY (VALUES
('Q1', r.Q1),
('Q2', r.Q2),
('Q3', r.Q3),
('Q4', r.Q4)
) AS v(Quarter, Revenue);Tip: CROSS APPLY VALUES is often more readable than both UNPIVOT and UNION ALL, especially when unpivoting many columns, and it handles NULLs more predictably than UNPIVOT (which silently removes NULL rows).
Worked Example
Monthly budget table with columns Dept, Jan, Feb, Mar — unpivot to Dept, Month, Budget:
SELECT Dept, Month, Budget
FROM budget
UNPIVOT (Budget FOR Month IN ([Jan],[Feb],[Mar])) AS u
ORDER BY Dept, Month;