Reference · SQL (DuckDB)
Pivot long data to wide in SQL (DuckDB)
Spread a key column into declared columns, one row per index, leaving absent combinations missing.
When to use it
Monthly or category readings that a report needs side by side.
How
Conditional aggregation with one expression per declared column.
max(CASE WHEN month = 'Jan' THEN CAST(value AS BIGINT) END) AS "Jan"
- No ELSE 0: an absent month stays NULL.
Pitfalls
- Filling gaps with zero.
- Letting each batch decide the columns.
Lesson: Pivot without inventing zeros
Practise it: Pivot monthly values, keeping zeros and gaps apart