To reshape rows into report columns is to turn a long table into a wide one: one column per month, one row per rep. SQL has no PIVOT statement in the standard, and every engine's version is different, but the portable way to build one is conditional aggregation — a SUM(CASE WHEN ...) per period, which is a technique you already know from module 04 and now use in a new shape.
How do you reshape rows into report columns?
The natural shape of this data is long: one row per rep per month. The shape a report wants is wide: one row per rep, one column per month.
SELECT r.rep_name, p.category, ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)), 2) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN product_lines p ON p.product_id = s.product_id
GROUP BY r.rep_id, r.rep_name, p.category
ORDER BY r.rep_name, p.category
LIMIT 8;
rep_name category revenue
------------- ----------- -------
Ada Whitfield accessories 1008.9
Ada Whitfield furniture 285.0
Ada Whitfield lighting 320.0
Ada Whitfield soft goods 240.0
Ada Whitfield storage 1065.0
Bruno Kessler accessories 1219.5
Bruno Kessler furniture 840.0
Bruno Kessler lighting 704.0
Long and tidy, and awkward for a reader who wants to scan across. The pivot turns it into the shape the spreadsheet expects:
SELECT r.rep_name,
ROUND(SUM(CASE WHEN s.sale_date >= '2025-03-01' AND s.sale_date < '2025-04-01' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS march,
ROUND(SUM(CASE WHEN s.sale_date >= '2025-04-01' AND s.sale_date < '2025-05-01' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS april,
ROUND(SUM(CASE WHEN s.sale_date >= '2025-05-01' AND s.sale_date < '2025-06-01' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS may
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
GROUP BY r.rep_id, r.rep_name
ORDER BY r.rep_name;
rep_name march april may
------------- ------ ------- --------
Ada Whitfield 1613.9 240.0 1065.0
Bruno Kessler 1440.0 619.5 1214.0
Carla Nunes 769.5 624.0 1110.0
Dev Patel 780.0 1077.0 318.6
Elif Demir 750.0 1397.25 480.0
Farid Haddad 512.0 1545.0 360.0
Greta Vogel 456.0 1416.0 1083.0
One row per rep, one column per month, and Ada's March figure of 1613.90 matches the running total from lesson 56. The mechanics are simple: each column is a SUM over a CASE expression that selects the rows belonging to that period and contributes zero to every other period. The ELSE 0 is what makes the row counts add up — without it, a rep with no sales in a month would produce NULL in that column, and NULL is not zero.
Does the same trick work for a non-date column?
Yes, and it is the more common case. The product categories become columns:
SELECT r.rep_name,
ROUND(SUM(CASE WHEN p.category = 'furniture' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS furniture,
ROUND(SUM(CASE WHEN p.category = 'storage' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS storage,
ROUND(SUM(CASE WHEN p.category = 'accessories' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS accessories,
ROUND(SUM(CASE WHEN p.category NOT IN ('furniture','storage','accessories') THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS other
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN product_lines p ON p.product_id = s.product_id
GROUP BY r.rep_id, r.rep_name
ORDER BY r.rep_name
LIMIT 5;
rep_name furniture storage accessories other
------------- --------- ------- ----------- -----
Ada Whitfield 285.0 1065.0 1008.9 560.0
Bruno Kessler 840.0 0.0 1219.5 1214.0
Carla Nunes 769.5 825.0 0.0 909.0
Dev Patel 357.0 780.0 1038.6 0.0
Elif Demir 812.25 1065.0 0.0 750.0
Five columns of numbers where there was one column and one dimension. Bruno sold no storage, so the cell is 0.0 — visible, and obviously a real zero rather than a gap. The catch is obvious from the query: the categories are written into the SQL. Add a category and the query has to change.
Is there a way to avoid hard-coding the categories?
Not for a true pivot in standard SQL — which is why every engine invented its own PIVOT syntax: Oracle's PIVOT, SQL Server's PIVOT, MySQL's dynamic pivot written with GROUP_CONCAT and prepared statements. But there is a window-function shape that attaches a total to every detail row without any hard-coded list:
SELECT r.rep_name, p.category, s.sale_date,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount))
OVER (PARTITION BY r.rep_id, p.category), 2) AS category_total
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN product_lines p ON p.product_id = s.product_id
WHERE r.rep_id = 1
ORDER BY p.category, s.sale_date;
rep_name category sale_date revenue category_total
------------- ----------- ---------- ------- --------------
Ada Whitfield accessories 2025-03-05 1008.9 1008.9
Ada Whitfield furniture 2025-03-24 285.0 285.0
Ada Whitfield lighting 2025-03-03 320.0 320.0
Ada Whitfield soft goods 2025-04-14 240.0 240.0
Ada Whitfield storage 2025-05-02 585.0 1065.0
Ada Whitfield storage 2025-05-19 480.0 1065.0
Every sale carries the total for its category, and the two storage rows both show 1065.00. This is not a pivot — it is a total attached to detail rows — but it answers the same question ("how much of Ada's business is storage?") without naming a single category in the SQL. The trade is real: conditional aggregation gives you the wide report a manager wants, while the window function gives you a shape that adapts to whatever values the data contains.
And the other direction?
Unpivoting — turning wide rows back into long ones — is what a reporting tool does when it exports a pivot for charting. In standard SQL it is a UNION ALL of the wide columns, each wrapped so that the column name becomes a row value. Here is the pivot above, given a name, and then unpivoted:
WITH monthly_pivot AS (
SELECT r.rep_name,
ROUND(SUM(CASE WHEN s.sale_date >= '2025-03-01' AND s.sale_date < '2025-04-01' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS march,
ROUND(SUM(CASE WHEN s.sale_date >= '2025-04-01' AND s.sale_date < '2025-05-01' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS april,
ROUND(SUM(CASE WHEN s.sale_date >= '2025-05-01' AND s.sale_date < '2025-06-01' THEN s.quantity * s.unit_price * (1 - s.discount) ELSE 0 END), 2) AS may
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
GROUP BY r.rep_id, r.rep_name
)
SELECT rep_name, 'march' AS period, march AS revenue FROM monthly_pivot
UNION ALL
SELECT rep_name, 'april', april FROM monthly_pivot
UNION ALL
SELECT rep_name, 'may', may FROM monthly_pivot;
rep_name period revenue
------------- ------ -------
Ada Whitfield march 1613.9
Bruno Kessler march 1440.0
Carla Nunes march 769.5
Dev Patel march 780.0
Elif Demir march 750.0
Farid Haddad march 512.0
Greta Vogel march 456.0
Ada Whitfield april 240.0
Bruno Kessler april 619.5
Carla Nunes april 624.0
Dev Patel april 1077.0
Elif Demir april 1397.25
Farid Haddad april 1545.0
Greta Vogel april 1416.0
Ada Whitfield may 1065.0
Bruno Kessler may 1214.0
Carla Nunes may 1110.0
Dev Patel may 318.6
Elif Demir may 480.0
Farid Haddad may 360.0
Greta Vogel may 1083.0
That is the reverse of the conditional aggregation, and it is worth recognising because it explains why a charting tool rarely wants a pivot at all: it wants the long form and does the reshaping itself.
Quick reference
- A pivot is conditional aggregation: one SUM(CASE WHEN ...) per output column.
- Use ELSE 0 rather than a bare ELSE, so empty periods show a zero instead of NULL.
- Conditional aggregation hard-codes the pivot values; a window function over a partition does not.
- MySQL, SQL Server and Oracle have PIVOT syntax; standard SQL does not.
- Unpivoting is a UNION ALL of the wide columns, with the column name becoming a value.
Pivot drill: build a wide report with one column per region and one row per rep, using conditional aggregation — then produce the same information in long form with a single GROUP BY, and say which of the two shapes you would hand to a charting tool.