Pivot & Unpivot

Pivoting turns rows into columns so a report can read across the page: one branch, one row, months across the top. Unpivoting does the reverse: it turns wide columns back into rows so SQL can filter, group, and chart them normally. Neither move changes the facts; it changes the shape of the answer.

Start from tidy rows

Reports usually start in a row-shaped table. This cafe table has one row per branch and month:

CREATE TABLE cafe_monthly_sales (
  branch text NOT NULL,
  sale_month date NOT NULL,
  revenue numeric(8,2) NOT NULL,
  tickets int NOT NULL,
  PRIMARY KEY (branch, sale_month)
);
 
INSERT INTO cafe_monthly_sales VALUES
  ('center', DATE '2026-01-01', 4200.50, 610),
  ('center', DATE '2026-02-01', 4580.75, 642),
  ('center', DATE '2026-03-01', 4895.00, 670),
  ('harbor', DATE '2026-01-01', 3120.25, 455),
  ('harbor', DATE '2026-03-01', 3355.50, 472),
  ('campus', DATE '2026-01-01', 2250.00, 388),
  ('campus', DATE '2026-02-01', 2410.25, 402),
  ('campus', DATE '2026-03-01', 2615.50, 429);

CREATE TABLE
INSERT 0 8

SELECT s.branch,
       s.sale_month,
       s.revenue
FROM cafe_monthly_sales AS s
ORDER BY s.branch, s.sale_month;

 branch | sale_month | revenue
--------+------------+---------
 campus | 2026-01-01 | 2250.00
 campus | 2026-02-01 | 2410.25
 campus | 2026-03-01 | 2615.50
 center | 2026-01-01 | 4200.50
 center | 2026-02-01 | 4580.75
 center | 2026-03-01 | 4895.00
 harbor | 2026-01-01 | 3120.25
 harbor | 2026-03-01 | 3355.50
(8 rows)

That shape is good database shape: each row says one thing at one grain. But it is not the shape a stakeholder usually means by "send me the Q1 branch report." They want months as columns.

Rows to columns with FILTER

The portable mental model is conditional aggregation: group to the row you want, then put each output column behind its own condition. PostgreSQL's FILTER syntax makes that readable:

SELECT s.branch,
       COALESCE(SUM(s.revenue) FILTER (WHERE s.sale_month = DATE '2026-01-01'), 0.00) AS jan_revenue,
       COALESCE(SUM(s.revenue) FILTER (WHERE s.sale_month = DATE '2026-02-01'), 0.00) AS feb_revenue,
       COALESCE(SUM(s.revenue) FILTER (WHERE s.sale_month = DATE '2026-03-01'), 0.00) AS mar_revenue,
       SUM(s.revenue) AS q1_revenue
FROM cafe_monthly_sales AS s
GROUP BY s.branch
ORDER BY s.branch;

 branch | jan_revenue | feb_revenue | mar_revenue | q1_revenue
--------+-------------+-------------+-------------+------------
 campus |     2250.00 |     2410.25 |     2615.50 |    7275.75
 center |     4200.50 |     4580.75 |     4895.00 |   13676.25
 harbor |     3120.25 |        0.00 |     3355.50 |    6475.75
(3 rows)

Read one column out loud: "sum the revenue, but only for January." GROUP BY branch decides one output row per branch; each filtered aggregate fills one month column. Harbor has no February row, so the February aggregate is NULL; COALESCE turns the missing month into a report zero.

The CASE version is the same idea in older clothes: SUM(CASE WHEN sale_month = DATE '2026-01-01' THEN revenue ELSE 0 END). FILTER is shorter, but the design is identical: one condition per output column.

Months across the top are a presentation choice

Pivoted reports are useful at the edge: dashboards, spreadsheets, slide tables, finance extracts. They are awkward as a storage shape. Once January, February, and March are separate columns, every query that wants "all months" has to name all three columns again, and adding April means changing the query shape. That is why the source table stays row-shaped and the pivot happens in the SELECT.

This is the general crosstab pattern:

1. Choose the row key: branch.
2. Choose the column keys: fixed months.
3. Choose the measure: SUM(revenue).
4. Write one conditional aggregate per output column.

The column keys must be known when you write the query. SQL returns a fixed set of columns; it does not invent new headers halfway through execution. Dynamic pivots exist, but they are usually application code or generated SQL, not beginner-friendly static SQL.

Columns back to rows with LATERAL VALUES

Unpivoting reverses a wide report into analyzable rows. Here the pivoted CTE makes a wide branch report, then CROSS JOIN LATERAL (VALUES ...) emits one row for each month column:

WITH pivoted AS (
  SELECT s.branch,
         COALESCE(SUM(s.revenue) FILTER (WHERE s.sale_month = DATE '2026-01-01'), 0.00) AS jan_revenue,
         COALESCE(SUM(s.revenue) FILTER (WHERE s.sale_month = DATE '2026-02-01'), 0.00) AS feb_revenue,
         COALESCE(SUM(s.revenue) FILTER (WHERE s.sale_month = DATE '2026-03-01'), 0.00) AS mar_revenue
  FROM cafe_monthly_sales AS s
  GROUP BY s.branch
)
SELECT p.branch,
       u.sale_month,
       u.revenue
FROM pivoted AS p
CROSS JOIN LATERAL (
  VALUES
    (DATE '2026-01-01', p.jan_revenue),
    (DATE '2026-02-01', p.feb_revenue),
    (DATE '2026-03-01', p.mar_revenue)
) AS u(sale_month, revenue)
ORDER BY p.branch, u.sale_month;

 branch | sale_month | revenue
--------+------------+---------
 campus | 2026-01-01 | 2250.00
 campus | 2026-02-01 | 2410.25
 campus | 2026-03-01 | 2615.50
 center | 2026-01-01 | 4200.50
 center | 2026-02-01 | 4580.75
 center | 2026-03-01 | 4895.00
 harbor | 2026-01-01 | 3120.25
 harbor | 2026-02-01 |    0.00
 harbor | 2026-03-01 | 3355.50
(9 rows)

The important part is not the syntax flourish; it is the direction. Pivoting makes a human-facing grid. Unpivoting gets data back to a shape SQL can analyze with ordinary WHERE, GROUP BY, joins, and windows.

Dialect helpers are shortcuts, not the model

PostgreSQL has a crosstab() helper in the tablefunc extension. DuckDB and Snowflake have PIVOT syntax. Those helpers can be convenient, but they are dialect features, and crosstab() requires an extension that is not the spine of this course playground. The portable fallback is the pattern you just wrote: conditional aggregates to pivot, and VALUES or UNION ALL to unpivot.

You now have the core Part IV reshaping tools: JSON documents, arrays/composites, text search, and row-column reshaping. Next comes performance, where the course opens the optimizer's view of how these queries actually run.