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
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.
Part by part
- SUM(CASE WHEN region = 'east' THEN amount ELSE 0 END)
- A conditional aggregate: contribute the amount when the row matches, zero otherwise. One of these per output column is what turns rows into columns.
- ELSE 0
- Makes non-matching rows contribute nothing to the sum. Omitting it yields NULL, which SUM ignores — the same result here, and not for COUNT.
- GROUP BY order_month
- One output row per month. Every column not aggregated must appear here, which is what limits how many dimensions a pivot can have.
Common mistakes
COUNT(CASE WHEN region = 'east' THEN 1 ELSE 0 END)COUNT counts non-NULL values, and zero is not NULL, so this counts every row regardless of region. Use SUM with the same expression, or COUNT with ELSE NULL.
COUNT(CASE WHEN region = 'east' THEN 1 END)SELECT order_month, region, SUM(amount) FROM orders GROUP BY order_month, regionNot wrong, and it is a different shape — one row per month per region rather than one row per month with a column each. That is usually easier to consume programmatically and harder to read on a page.
Adding a new region without editing the queryThe columns are hard-coded, so a fourth region simply does not appear and its revenue vanishes from the report without any error. Every pivot written this way needs revisiting when the dimension changes.
Questions
Why not use a PIVOT operator?
Because it is not standard SQL. SQL Server and Oracle have one, Postgres has crosstab in an extension, and MySQL and SQLite have nothing — the CASE form works identically everywhere, which is why it persists.
How do I pivot an unknown set of values?
You cannot in plain SQL, because the column list must be fixed before the query runs. Either generate the SQL dynamically from a prior query, or return the long format and pivot in the application layer.
Does ELSE 0 matter for SUM?
Not for the total, since SUM skips NULLs. It matters when every row for a group is non-matching: with ELSE 0 you get 0, without it you get NULL, and those display very differently in a report.
Is this efficient?
It reads the table once and evaluates each CASE per row, which is generally cheaper than several separate aggregate queries joined together. The cost grows with the number of columns rather than the number of passes.