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