Reshaping rows into report columns means turning a long table into a wide one. There is one column for each month, and one row for each rep. The SQL standard has no PIVOT statement, and every engine has its own version. The portable way to build one is conditional aggregation, which is a SUM(CASE WHEN ...) for each period. You already know that technique from module 04, and now you use it in a new shape. The whole job fits inside one aggregate expression.
The previous lesson in this module covered "How Do LAG and LEAD Compare Rows to Previous or Next?", and this lesson is the last one in the Window Functions module.
How do you reshape rows into report columns?
The natural shape of this data is long. There is one row for each rep for each month, and one row for each rep for each category. Each row carries a single number and a name that says what the number is.
The shape that a report wants is wide. There is one row for each rep, and one column for each 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.0It is long and it is tidy, and it is awkward for a reader who wants to scan across. A manager reading a spreadsheet wants the months across the top.
The pivot turns it into the shape that 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.0There is one row for each rep, and one column for each month. 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 that contributes zero to every other period. The date ranges inside the CASE are what pick the rows, so the column name and the range have to agree.
The ELSE 0 is what makes the row counts add up. Without it, a rep with no sales in a month would produce a NULL in that column, and a NULL is not zero. A NULL in a report is also a pain to sort and to add up.
Does the same trick work for a non-date column?
It does, and it is the more common case. The product categories become the columns. The same expression does the work, and only the condition changes.
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.0There are five columns of numbers where there used to be one column and one dimension.
Bruno sold no storage, so the cell is 0.0. It is visible, and it is obviously a real zero and not a gap.
The catch is obvious from the query. The categories are written into the SQL. You add a category, and the query has to change. Nothing in the data stops you, so the query and the data have to be kept in step.
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. There is the PIVOT of Oracle, the PIVOT of SQL Server, and the dynamic pivot of MySQL that is written with GROUP_CONCAT and prepared statements. Every one of them is a shortcut for the conditional aggregation.
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.0Every sale carries the total for its category, and the two storage rows both show 1065.00. The total does not care how many rows there are in the group.
This is not a pivot. It is a total attached to the detail rows. But it answers the same question, which is "how much of Ada's business is storage?", without naming a single category in the SQL.
The trade is real. The conditional aggregation gives you the wide report that a manager wants, while the window function gives you a shape that adapts to whatever values the data contains. Choose the wide report when a person will read it, and choose the long report when a program will read it.
And the other direction?
Unpivoting turns the wide rows back into the long ones. That is what a reporting tool does when it exports a pivot for charting. The long form is the safer one, because it keeps every number.
In standard SQL it is a UNION ALL of the wide columns, where each one is 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.0That 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 it does the reshaping itself. The same numbers appear in both forms, so you can check one against the other.
Quick reference
- A pivot is the conditional aggregation, which is one
SUM(CASE WHEN ...)for each output column. - You use the
ELSE 0and not a bareELSE, so that the empty periods show a zero and not aNULL. - Conditional aggregation hard-codes the pivot values, and a window function over a partition does not.
- MySQL, SQL Server and Oracle have the
PIVOTsyntax, and standard SQL does not. - Unpivoting is a
UNION ALLof the wide columns, with the column name becoming a value.
Try it yourself: pivot drill. Build a wide report with one column per region and one row per rep, using the conditional aggregation. Then produce the same information in the long form with a single GROUP BY, and say which of the two shapes you would hand to a charting tool.