Skip to content
Tutorials Center
My learning

Topics

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).

SQL
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.

SQL Orders per status for each customer ยท database: shop

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.

SQL Average grade per course and term ยท database: school

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.

SQL Columns back into rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Dialect differences

SQL
-- 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. COUNT for counts, SUM for totals, MAX when each cell holds one value (the "row to column" trick for key/value tables).
  • Missing combinations give NULL for MAX/AVG and 0 for COUNT. Wrap in COALESCE for 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(*) or SUM(...) without a CASE, as in the first example.
  • Presentation may belong elsewhere. Reporting tools and spreadsheets pivot easily; SQL is best for the aggregation.