Pivot / Unpivot
SQL · Extra Concepts
Theory
PIVOT turns values that sit in rows into separate columns. A sales table with one row per (city, quarter) becomes a report with one row per city and columns Q1, Q2, Q3. UNPIVOT does the reverse: it turns those quarter columns back into rows, which is often needed before loading a spreadsheet-style file into a proper table.
SQL Server, Oracle and Snowflake have a native PIVOT clause, but the column list must usually be written out in advance. The portable method that works everywhere, including SQLite, MySQL and PostgreSQL, is conditional aggregation: GROUP BY the row label and write one SUM(CASE WHEN quarter = 'Q1' THEN amount END) AS Q1 per output column. CASE returns NULL for non-matching rows, and SUM ignores NULLs, so each column only adds its own quarter. Combinations with no data come out as NULL; wrap them in COALESCE(..., 0) if you want zeros. UNPIVOT can be emulated with UNION ALL of one SELECT per column.
Why we use it
Reports and dashboards are usually wide (one column per month or category), while databases store data long (one row per fact). PIVOT reshapes long data into the readable cross-tab people expect.
When to use it
Use PIVOT or CASE-based pivots for final report layouts with a known, small set of columns. Keep data long for storage and further analysis. For a changing set of categories, pivot in the BI tool or build the SQL dynamically.
How it works
- Start with long data: one row per city and quarter
- Choose the row label (city) and the column source (quarter)
- GROUP BY the row label
- For each output column: SUM(CASE WHEN quarter = 'Q1' THEN amount END)
- Any city-quarter with no data? Yes: NULL — COALESCE(..., 0). No: Keep the totals.
- Wide report: one row per city
Syntax
SELECT * FROM table
PIVOT (AGG(value) FOR column IN (val1, val2))Example
SELECT
SUM(CASE WHEN department='IT' THEN salary END) AS IT,
SUM(CASE WHEN department='HR' THEN salary END) AS HR
FROM employees;Key points
- SQL Server/Oracle have native PIVOT syntax; most other databases emulate it with CASE + GROUP BY
- The CASE + SUM/COUNT pattern works in virtually every SQL dialect, including SQLite
- Missing combinations come out as NULL — COALESCE them to 0 if needed
- Native PIVOT needs the column list written out in advance
- UNPIVOT can be emulated with UNION ALL, one SELECT per column