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.
The starting shape: long format
Here's the raw data, five separate rows, with region and quarter both describing what each amount is:
| id | region | quarter | amount |
|---|---|---|---|
| 1 | North | Q1 | 1000 |
| 2 | North | Q2 | 1200 |
| 3 | South | Q1 | 800 |
| 4 | South | Q2 | 950 |
| 5 | North | Q1 | 500 |
To compare Q1 vs Q2 side by side, a human has to scan across rows. A report wants that comparison in columns instead.
One row per region, per quarter
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):
| region | q1_total | q2_total |
|---|---|---|
| South | 800 | 950 |
| North | 1500 | 1200 |
North's q1_total is 1500, that's 1000 + 500 from the two North/Q1 rows, correctly combined by the SUM.
Long data, pivoted wide
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.
A running grand-total column, same pattern
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. It computes exactly the same result as the CASE WHEN ... ELSE 0 END pattern, just 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.
Same pivot, Postgres FILTER syntax
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.