Rotating rows into columns
A pivot reshapes long data into wide data, turning distinct values of one column into separate output columns. While some databases offer a PIVOT keyword, the portable technique is conditional aggregation using CASE expressions inside aggregates.
The CASE inside aggregate pattern
The idea is to wrap a CASE expression in an aggregate like SUM or MAX, one per target column:
- SUM CASE WHEN month equals Jan THEN amount ELSE zero END as jan total.
- Repeat for each month you want as a column.
- GROUP BY the row label, such as product.
Each CASE contributes only when its condition matches, so every output column sums just the rows for that category. The surrounding GROUP BY collapses many rows per product into a single wide row.
Why ELSE zero matters
Without an ELSE clause the CASE returns null for non matching rows. SUM ignores nulls, so it still works, but using ELSE zero makes intent clear and avoids surprises with COUNT or AVG.
Limitation
You must list each target column by hand, so the set of columns is fixed at write time. Truly dynamic pivots require generating SQL or a database specific feature.
Key idea
Conditional aggregation pivots data by wrapping CASE expressions in aggregates and grouping by the row label, producing one wide row per group with a fixed set of columns.