PatternSQL
Pivot rows into columns
Turn one category column into one column per category — revenue by region as a wide row — with SUM(CASE WHEN ...) instead of a vendor PIVOT clause.
Last updated
Pattern
SELECT order_month, SUM(CASE WHEN region = 'east' THEN amount ELSE 0 END) AS east, SUM(CASE WHEN region = 'west' THEN amount ELSE 0 END) AS west, SUM(CASE WHEN region = 'north' THEN amount ELSE 0 END) AS north FROM orders GROUP BY order_month;
Why it’s written this way
Each CASE expression evaluates to a row's amount only when that row matches its region, and to 0 otherwise. Wrapping it in SUM per group collapses those per-row values into one number per order_month — run the same idea three times with three conditions and three columns come out instead of three sets of rows.
Because this is just aggregation and not a special pivot syntax, it runs identically on DuckDB, Postgres and MySQL — unlike vendor-specific PIVOT clauses, which vary in syntax or don't exist at all.
Edge cases to know
- →The category list (which region values become columns) has to be known ahead of time and typed into the query — this doesn't discover new categories on its own the way a dynamic pivot tool would.
- →Use 0 in the ELSE branch, as written, when a region had no orders that month should read as zero; leave it NULL instead if 'zero' and 'no data' need to stay distinguishable downstream.
- →A typo in a literal (say 'esat' instead of 'east') produces a silent column of zeros rather than an error — worth checking SELECT DISTINCT region first if the values aren't already known.