PIVOT
Turn the distinct values of a column into separate output columns. DuckDB (and SQL Server, Snowflake) offers a built-in PIVOT statement. Elsewhere, emulate it with conditional aggregation: SUM(CASE WHEN col = 'a' THEN metric END) AS a. Useful for wide reporting output; rarely a good shape for downstream joins.
Syntax #
-- Native DuckDB form (SQL Server / Snowflake have PIVOT too,
-- but as FROM ... PIVOT (agg FOR col IN (...)) — different shape).
PIVOT source_table
ON pivot_col
USING aggregate_expr
GROUP BY row_col;
-- Portable equivalent (any engine)
SELECT
row_col,
SUM(CASE WHEN pivot_col = 'a' THEN metric END) AS a,
SUM(CASE WHEN pivot_col = 'b' THEN metric END) AS b
FROM source_table
GROUP BY row_col;Example #
PIVOT is a presentation convenience, not a modeling choice. Pivoted tables have a schema that depends on the data: paid, pending, cancelled columns, and if tomorrow's data has refunded, the output widens. That breaks everything downstream: joins expect stable column names, BI tools cache schemas, dashboards hardcode field references. Pivot at the last mile (the final report), not in intermediate steps. For the in-between, keep data long (one row per key/metric) and pivot only when a human or a spreadsheet needs it wide.