UNPIVOT
Turn multiple columns into name/value pairs: one row per (key, column_name, column_value). The inverse of PIVOT, it narrows wide tables into long form so you can aggregate, filter, or chart across metrics uniformly. Without UNPIVOT, the portable equivalent is a UNION ALL per column.
Syntax #
-- Native DuckDB form (SQL Server / Snowflake have UNPIVOT too,
-- but as FROM ... UNPIVOT (val FOR name IN (...)) — different shape).
UNPIVOT source_table
ON col_a, col_b, col_c
INTO NAME metric_name VALUE metric_value;
-- Portable equivalent (any engine)
SELECT key, 'col_a' AS metric_name, col_a AS metric_value FROM source_table
UNION ALL
SELECT key, 'col_b', col_b FROM source_table
UNION ALL
SELECT key, 'col_c', col_c FROM source_table;Example #
All unpivoted columns must have compatible types. UNPIVOT total, quantity only works if both can go into one value column. Otherwise the engine either errors or silently casts, which can lose precision. Cast explicitly before unpivoting (CAST(total AS DOUBLE)) or split into two separate unpivots by type. Second caveat: NULL values are included by default, so add EXCLUDE NULLS (or filter with WHERE val IS NOT NULL after) if the wide form used NULL as "no value here."