Chapter 25 · advanced · 8 min
Pivoting: rows into columns
Data usually arrives in long format: one row per measurement, like one row per region per quarter. Reports usually want wide format: one row per region, with a separate column for each quarter. Some databases have a native PIVOT keyword; plain Postgres doesn't, but you can build the exact same result with tools you already know: CASE WHEN inside an aggregate, inside a GROUP BY.
We'll turn quarterly sales into a side-by-side comparison.
The dataset
A sales table in long format: one row per region, per quarter.
Schema
| id | INT |
| region | TEXT |
| quarter | TEXT |
| amount | NUMERIC |
Example data
The starting shape: long format
Here's the raw data: five separate rows, with region and quarter both describing what each amount is. To compare Q1 vs Q2 side by side, a human has to scan across rows. A report wants that comparison in columns instead.
Before you run it — North appears in two separate rows for Q1 (1000 and 500). Will they show as separate rows, or already combined?
The pivot pattern
The trick: instead of one plain SUM(amount), write one conditional SUM per target column. SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) only adds up the Q1 rows, contributing 0 for everything else. Group by the dimension that should become your rows (region), not the one becoming columns (quarter).
Before you run it — North has two Q1 rows, 1000 and 500. What q1_total do you expect for North?
Adding another column is just adding another CASE
The pattern scales linearly: a third quarter means one more SUM(CASE WHEN ...) expression in SELECT, nothing else about the query changes. Compare that to reshaping the data outside SQL, which usually means rewriting the whole report.
Before you run it — for North, q1_total + q2_total should equal grand_total. What's North's grand_total?
A Postgres shortcut: FILTER
Postgres offers a more concise alternative to CASE WHEN inside an aggregate: FILTER (WHERE condition), attached directly to the aggregate function, and shorter to write. The catch: FILTER is Postgres-specific, not standard SQL, so it won't work on every database. CASE WHEN is the portable choice to know; FILTER is a nice shortcut to use when you know you're on Postgres.
Before you run it — does FILTER produce the same q1_total/q2_total values as the CASE WHEN version earlier, or different ones?
If you've used Excel or Google Sheets
This entire chapter is doing, in one query, what a spreadsheet's PivotTable UI does by dragging region into Rows, quarter into Columns, and amount into Values as "Sum". The advantage of the SQL version is that it's a query, not a manual UI configuration. It re-runs identically on fresh data with zero re-dragging, and it can feed directly into another query or a dashboard.
Ready to practice? GoTo's quarterly sales pivot question below is this exact pattern, one dataset over.
This is the one "trick" every SQL interviewer expects you to know without being told: whenever a question asks for one row per group with separate columns per category, reach for SUM(CASE WHEN category = 'X' THEN value ELSE 0 END) (or Postgres's FILTER), one per column.