SQLsqlpivotunpivotreportingproduction-ready

Pivot and Unpivot Patterns

SQL techniques for transforming rows to columns (pivot) and columns to rows (unpivot) for reporting and data reshaping.


title: "Pivot and Unpivot Patterns" description: "SQL techniques for transforming rows to columns (pivot) and columns to rows (unpivot) for reporting and data reshaping." category: "SQL" language: "sql" tags: ["sql", "pivot", "unpivot", "reporting", "production-ready"] createdAt: "2025-05-01" updatedAt: "2025-05-15"

Overview

Techniques for reshaping data between row and column orientations. Pivoting converts category values into columns for cross-tab reports, while unpivoting normalizes wide tables into tall format for analysis and visualization.

Code

-- Pivot with CASE
SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1
 
-- Unpivot with LATERAL
CROSS JOIN LATERAL (VALUES ('col1', t.col1), ('col2', t.col2)) AS u(name, value)

Notes

  • CASE-based pivot works in all SQL databases (most portable approach)
  • FILTER (WHERE ...) is PostgreSQL syntax for conditional aggregation (cleaner than CASE)
  • LATERAL join unpivot is more maintainable than UNION ALL when adding/removing columns
  • crosstab() in PostgreSQL handles truly dynamic pivots where column count varies
  • For reporting tools, unpivoted (tall) format is usually preferred as it scales better