PIVOT

PIVOT rotates rows into columns — turning distinct values from one column into separate output columns, typically paired with an aggregate. UNPIVOT does the reverse, turning columns back into rows.

A classic use: turning a tall (department, year, revenue) table into a wide report with one column per year. Oracle and SQL Server both provide a native PIVOT operator; PostgreSQL and MySQL achieve the same result with conditional aggregation (SUM(CASE WHEN year = 2025 THEN revenue END)), which is more portable but more verbose.

Pivoting is a presentation transformation, not a storage one — the underlying table stays normalized; only the query output reshapes it.

SELECT *
FROM (SELECT department_id, year, revenue FROM sales)
PIVOT (SUM(revenue) FOR year IN (2024, 2025, 2026));