Unpivoting Columns Into Rows
Reversing wide tables with UNPIVOT or UNION ALL.
The Reverse Problem
Unpivoting is the mirror image of pivoting: you take a wide table and turn its columns back into rows. Interviewers ask this when data arrives in spreadsheet shape but needs to be normalized for analysis.
Example: a table with columns q1, q2, q3, q4 per region must become rows of (region, quarter, amount). This long form is what aggregation, joining, and charting all prefer.
-- Wide input we want to unpivot
region | q1 | q2 | q3 | q4
-------+-----+-----+-----+----
East | 100 | 150 | 120 | 180
West | 200 | 250 | 210 | 260The Portable UNION ALL Pattern
The dialect-independent answer is UNION ALL: write one SELECT per source column, each emitting a literal label and that column's value.
Use UNION ALL, not UNION, so you do not pay for deduplication and you keep every row, even when two cells share a value.
SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;All lessons in this course
- Pivoting With Conditional Aggregation
- Vendor PIVOT and Crosstab Syntax
- Unpivoting Columns Into Rows
- Dynamic Pivots With Unknown Columns