SQL Reference ยท JSON and Special Features
SQL pivoting data
Concept guide to pivoting in SQL: turn rows into columns with conditional aggregation, SQL Server and Oracle PIVOT, PostgreSQL FILTER, and unpivoting.
Pivoting turns values from rows into column headings: instead of one row per (department, year) you get one row per department with a column for each year. It is a report-shaping operation, common for cross-tabs and spreadsheets-style output. The reverse, unpivoting, turns columns back into rows.
SQL has no portable PIVOT keyword. SQL Server and Oracle added one, but the universal technique, which works in every database, is conditional aggregation: a GROUP BY where each output column aggregates only the rows that match one value, using CASE (or FILTER).
SELECT department_id, SUM(CASE WHEN title = 'Engineer' THEN 1 ELSE 0 END) AS engineers, SUM(CASE WHEN title = 'Designer' THEN 1 ELSE 0 END) AS designers FROM employees GROUP BY department_id;
Pivot with conditional aggregation
The pivoted columns must be listed in the query, so the set of values has to be known when you write it. Each SUM(CASE ...) adds up only the matching rows.
Press Run (or Ctrl + Enter) to see the result here.
COUNT(CASE WHEN ... THEN 1 END) counts only rows for which the CASE is not NULL; the missing ELSE returns NULL, which COUNT ignores.
Press Run (or Ctrl + Enter) to see the result here.
When a term has no grades for a course, the average is NULL. Use COALESCE if you want a placeholder.
Unpivoting
To turn columns into rows, UNION ALL one select per column. It works everywhere.
Press Run (or Ctrl + Enter) to see the result here.
Dialect differences
-- SQL Server and Oracle: PIVOT operator SELECT * FROM (SELECT customer_id, status, id FROM orders) AS src PIVOT (COUNT(id) FOR status IN ([pending], [shipped], [delivered], [cancelled])) AS p; -- Oracle writes IN ('pending' AS pending, 'shipped' AS shipped, ...) -- SQL Server and Oracle: UNPIVOT SELECT customer_id, status, orders FROM wide_table UNPIVOT (orders FOR status IN (pending, shipped, delivered, cancelled)) AS u; -- PostgreSQL: FILTER clause (cleaner than CASE) SELECT customer_id, COUNT(*) FILTER (WHERE status = 'pending') AS pending, COUNT(*) FILTER (WHERE status = 'shipped') AS shipped FROM orders GROUP BY customer_id; -- PostgreSQL: crosstab() from the tablefunc extension -- MySQL: conditional aggregation (no PIVOT); build the column list with dynamic SQL
| Database | Native pivot | Notes |
|---|---|---|
| SQLite | No | Conditional aggregation; FILTER works in SQLite 3.30+. |
| PostgreSQL | crosstab() extension |
FILTER is the idiomatic way. |
| MySQL | No | SUM(CASE ...) or SUM(status = 'x'). |
| SQL Server | PIVOT / UNPIVOT |
Column list is fixed unless you build the SQL dynamically. |
| Oracle | PIVOT / UNPIVOT, PIVOT XML for dynamic lists |
Values are listed in the IN (...). |
Usage notes
- The columns are fixed at write time. If new status values can appear, the pivot silently ignores them (or you generate the SQL from the distinct values in application code or dynamic SQL).
- Choose the aggregate on purpose.
COUNTfor counts,SUMfor totals,MAXwhen each cell holds one value (the "row to column" trick for key/value tables). - Missing combinations give
NULLforMAX/AVGand0forCOUNT. Wrap inCOALESCEfor reports. - Pivot late. Do joins and filters first, pivot in the final step, and keep the narrow (row-per-fact) shape in your tables.
- Totals. Add a total column with
COUNT(*)orSUM(...)without aCASE, as in the first example. - Presentation may belong elsewhere. Reporting tools and spreadsheets pivot easily; SQL is best for the aggregation.
Related
- CASE, GROUP BY, aggregate FILTER, COUNT(), UNION
- Lessons: Pivot tables, CASE, Grouping in practice