Reference · SQL (DuckDB)
Unpivot wide data to long in SQL (DuckDB)
Turn several columns into key/value rows, without inventing rows for missing values.
When to use it
Survey or spreadsheet exports with one column per question or period.
How
DuckDB's UNPIVOT statement. It drops NULL values unless you add INCLUDE NULLS.
UNPIVOT survey ON q1, q2, q3 INTO NAME question VALUE answer
- Cast the value column in an outer SELECT.
Pitfalls
- Scoring missing answers as 0.
- Mixing types in the unpivoted columns.
Lesson: Unpivot without inventing scores
Practise it: Unpivot survey answers without inventing scores