Recipe

Columns to rows (unpivot)

Short answer: turn many columns into rows. The portable form: UNION ALL a SELECT per column, each tagged with a category label. Modern engines (DuckDB / Snowflake / BigQuery / SQL Server) have native UNPIVOT syntax that's cleaner. The native form is shorter; the UNION ALL form works everywhere and is more flexible. Use unpivot to turn a wide report-shaped table into the tall, normalized shape that SQL aggregates and joins prefer.

Canonical SQL #

sql

-- Portable: UNION ALL.
SELECT region, 'q1' AS quarter, q1_revenue AS revenue FROM revenue_wide
UNION ALL
SELECT region, 'q2',            q2_revenue          FROM revenue_wide
UNION ALL
SELECT region, 'q3',            q3_revenue          FROM revenue_wide
UNION ALL
SELECT region, 'q4',            q4_revenue          FROM revenue_wide;

-- DuckDB / Snowflake / SQL Server: native UNPIVOT.
SELECT region, quarter, revenue
FROM   revenue_wide
UNPIVOT (
  revenue
  FOR quarter IN (q1_revenue AS 'q1',
                  q2_revenue AS 'q2',
                  q3_revenue AS 'q3',
                  q4_revenue AS 'q4')
);

-- Postgres / DuckDB: VALUES + LATERAL trick.
SELECT region, quarter, revenue
FROM   revenue_wide,
LATERAL (VALUES
  ('q1', q1_revenue),
  ('q2', q2_revenue),
  ('q3', q3_revenue),
  ('q4', q4_revenue)
) AS t(quarter, revenue);

Example #

Loading SQL editor...

When to unpivot #

You're consuming a "report-shaped" export, a CSV with one column per month or per metric. Unpivot to make it queryable. This is the most common case in analytics pipelines.

You inherit a wide table built before "use a date column" was an option (e.g., revenue_2024, revenue_2025, revenue_2026). Every analysis would otherwise require updating the SQL each year. Unpivot to a tall shape; aggregate by year as just another GROUP BY.

You're feeding a chart library that expects long-form data (most of them do — Vega, ggplot, Altair). Unpivot at the query layer rather than reshaping in code.

Avoid unpivoting and storing the result. Tall tables are queryable but storage-inefficient versus normalized models: three columns ("region, quarter, revenue") with four rows per region beats one row with four columns only at query time. For storage, prefer the proper grain: one row per region per quarter from the start.

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`LATERAL (VALUES (...), (...)) AS t(label, val)`	no native `UNPIVOT`
MySQL	`UNION ALL` per column	no native `UNPIVOT`
SQL Server	`UNPIVOT (val FOR label IN (c1, c2))`	drops `NULL` rows by default
BigQuery	`UNPIVOT (val FOR label IN (c1, c2))`	add `INCLUDE NULLS` to keep `NULL` source values
Snowflake	`UNPIVOT (val FOR label IN (c1, c2))`	`NULL`s excluded unless you opt in
Oracle	`UNPIVOT (val FOR label IN (c1, c2))`	`EXCLUDE NULLS` is the default; 11g+
SQLite	`UNION ALL` per column	no native `UNPIVOT`
DuckDB	`UNPIVOT t ON c1, c2 INTO NAME label VALUE val`	—

Dialect notes #

Postgres / SQLite: no native UNPIVOT. Use the UNION ALL form, or LATERAL VALUES for compactness on Postgres.
DuckDB: UNPIVOT syntax (UNPIVOT t ON col1, col2, col3 INTO NAME label VALUE value). Cleaner than UNION ALL for many columns.
Snowflake: native UNPIVOT (col_name FOR alias IN (col1, col2)). Also UNPIVOT_INCLUDE_NULLS.
BigQuery: UNPIVOT works similarly. Also UNPIVOT INCLUDE NULLS keeps rows where the source column was NULL.
SQL Server: UNPIVOT (and PIVOT) since SQL Server 2005. Discards NULLs by default.
Oracle: UNPIVOT (11g+); explicit INCLUDE NULLS / EXCLUDE NULLS clause.
MySQL: no native UNPIVOT. UNION ALL only.

Tip

Wide-and-tall is the daily reshape work in analytics. Wide tables are for humans reading; tall tables are for databases querying. The two pages on this site that capture the round trip: Rows to columns (pivot) and this page. A typical analysis often goes tall → group by → wide for display: get the data into the queryable shape, do the analysis, pivot at the very end for presentation. Don't fight the shape. Reshape it twice if the workflow needs it; reshapes are cheap.