PIVOT and UNPIVOT
Reshape rows to columns with PIVOT, and columns to rows with UNPIVOT.
PIVOT and UNPIVOT are the SQL form of reshape. For the DataFrame API form, see Python DataFrame — Reshape and Rust DataFrame — Group / Aggregate.
PIVOT
Rotates rows to columns, aggregating values that share a key.
SELECT *
FROM orders
PIVOT (
SUM(amount) FOR region IN ('us-east', 'us-west', 'eu')
) AS p (order_id, customer_id, us_east, us_west, eu);
The AS p (...) clause names the output columns. Columns not in the PIVOT key (order_id, customer_id) are passed through unchanged.
Multiple aggregates
SELECT *
FROM orders
PIVOT (
SUM(amount) AS total_amount,
COUNT(*) AS n
FOR region IN ('us-east' AS us_east, 'eu' AS eu)
) AS p (order_id, customer_id, us_east_total, us_east_n, eu_total, eu_n);
UNPIVOT
The reverse: columns to rows. Useful for turning wide results (one column per period) into long format (a period column and a value column).
SELECT *
FROM monthly_totals
UNPIVOT (
total FOR month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
);
Result columns: region (passed through), month (the literal label), total (the value).
NULL handling in UNPIVOT
By default, UNPIVOT excludes rows where the source column is NULL. Use UNPIVOT INCLUDE NULLS (where supported) to keep them:
SELECT *
FROM monthly_totals
UNPIVOT INCLUDE NULLS (
total FOR month IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec)
);