Pivoting & Cross-Tab
Pivoting & Cross-Tab — Transform rows to columns with CASE pivoting, PIVOT operator, crosstab function, and UNPIVOT. The guide walks through CASE Pivot (Manual), PIVOT Operator, Crosstab (PostgreSQL), UNPIVOT / Reverse Pivot, Dynamic Pivoting. The most portable pivoting method uses CASE expressions with aggregation. Syntax: SELECT department, SUM(CASE WHEN year = 2022 THEN amount END) AS y2022, SUM(CASE WHEN year = 2023 THEN amount END) AS y2023, SUM(CASE WHEN year = 2024 THEN amount END) AS y2024 FROM sales GROUP BY department. This works in every database and offers full control over output columns. SQL Server and Oracle support the PIVOT operator for concise pivoting. Syntax: SELECT * FROM (SELECT year, department, amount FROM sales) AS src PIVOT (SUM(amount) FOR year IN ([2022], [2023], [2024])) AS pvt. The IN clause must list all values explicitly. Dynamic PIVOT requires string building and sp_executesql (SQL Server) or EXECUTE IMMEDIATE (Oracle). The guide is organized into 5 sections that build on each other, each pairing a prose explanation with real SQL you can run as-is.