All lessons

Module 8: Patterns analysts use every week · Lesson 8.6 · about 25 min

Pivoting rows into columns

Turn a long table (one row per department and year) into a wide one (one column per year) with SUM(CASE WHEN ...), the pivot that works in every database. Plus unpivoting back with UNION ALL.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 25 minutes · read first: 2.6, 3.2. You’ll be able to turn rows into columns with SUM(CASE WHEN …), and turn columns back into rows with UNION ALL.

A pivot turns the values of one column into column headings by putting a CASE inside each SUM, so each column only adds up its own rows.

In everyday terms: sorting the post into pigeonholes. Every letter goes past every hole, but each hole only keeps the letters with its own label.

1. Why this matters

Databases like long tables: one row per department per year. People like wide tables: one row per department, one column per year, so they can compare across. Managers ask for “a table with years across the top” every week. You can do it in Excel, but doing it in SQL means the report updates itself and the numbers can’t be mistyped.

2. Long and wide

Here is revenue in long form: one row for each department and year.

-- try
SELECT c.department,
       strftime('%Y', o.order_date) AS year,
       ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount)), 2) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.department, year
ORDER BY c.department, year

Eight rows: four departments × two years. To compare 2024 with 2025 for one department, your eye has to jump between rows. Pivoting turns the values of one column (year) into column headings.

3. The CASE pivot

The trick: group by the thing you want as rows (department), and for each column you want, write one aggregate with a CASE inside that only lets that column’s rows through.

-- try
SELECT c.department,
       ROUND(SUM(CASE WHEN strftime('%Y', o.order_date) = '2024'
                      THEN i.quantity * i.unit_price * (1 - i.discount) ELSE 0 END), 2) AS rev_2024,
       ROUND(SUM(CASE WHEN strftime('%Y', o.order_date) = '2025'
                      THEN i.quantity * i.unit_price * (1 - i.discount) ELSE 0 END), 2) AS rev_2025
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
JOIN categories c ON c.category_id = p.category_id
WHERE o.status = 'completed'
GROUP BY c.department
ORDER BY rev_2025 DESC

Four rows, two year columns. Grocery is the biggest department in both years ($20,817.95 then $27,876.18). This pattern is sometimes called conditional aggregation, and it works in every SQL database.

To count instead of sum, let the CASE produce 1 or 0. Watch it on the mini tables first: press Show how it runs and see each order’s 1s and 0s land in the city’s columns.

-- try
SELECT c.city,
       SUM(CASE WHEN o.amount >= 40 THEN 1 ELSE 0 END) AS big_orders,
       SUM(CASE WHEN o.amount < 40 THEN 1 ELSE 0 END) AS small_orders
FROM mini_orders o
JOIN mini_customers c ON c.id = o.customer_id
GROUP BY c.city

Toronto has three big orders (two kettles and the duvet) and one small one (the bagels); Ottawa has one small order.

Maths and principle

Principle: a pivot is a grouping followed by splitting one aggregate into several, each restricted to one value of the pivot column. The CASE is a filter that lives inside the aggregate instead of in WHERE.

Equation: for each department d and year y, cell(d, y) = Σ value · [year = y], where [·] is 1 when true and 0 when false (the Iverson bracket). So γdepartment; SUM(value·[year=2024]), SUM(value·[year=2025]). Adding the columns back together gives the plain total, because the brackets for all years add up to 1 on every row.

Analogy: sorting post into pigeonholes. Every letter passes every pigeonhole, but each hole only keeps letters with its own label.

Try it: check the Iverson idea on the result above. Is Grocery’s rev_2024 + rev_2025 equal to its total revenue? (20,817.95 + 27,876.18 = 48,694.13; run a plain SUM grouped by department to see.)

Long table
One row per thing per value, such as one row per department per year. Easy to group and store.
Wide table
One row per thing, one column per value, such as a column per year. Easy to read across.
SUM(CASE WHEN year = 2025 THEN x ELSE 0 END)
Adds x only on 2025 rows; every other row adds 0.
Conditional aggregation
The name for an aggregate with a CASE inside it.
Unpivot
Wide back to long: one SELECT per column, stacked with UNION ALL.
Physical meaning
GROUP BY decides the rows of the result (one per department). Each CASE column sees every row of its group but lets only one year's values into its SUM, so the group's total is split across the columns, and the columns add back up to the total.

“Put the year in WHERE to make the 2025 column.” WHERE removes rows for the whole query, so the 2024 column would be empty. The filter for each column has to live inside that column’s aggregate, as a CASE.

Watch it happen, one step at a time:

Long table.
departmentyearrevenue
Grocery202420
Grocery202530
Home202415
Home202510
CASE on each row. Each row sends its revenue to one column and 0 to the other.
departmentrev_2024rev_2025
Grocery200
Grocery030
Home150
Home010
GROUP BY department and SUM. The zeros vanish into the totals.
departmentrev_2024rev_2025
Grocery2030
Home1510
Check. 20 + 30 = 50, the plain Grocery total. The columns always add back up to the total.

Any live box with a SELECT has a Show how it runs button that animates the real query on Northbay, row by row.

4. Unpivot: wide back to long

Sometimes the data arrives wide (a spreadsheet with a column per month) and you need it long, to group or chart it. Stack one SELECT per column with UNION ALL (Lesson 4.7).

-- try
WITH wide AS (
  SELECT 'Grocery' AS department, 20817.95 AS rev_2024, 27876.18 AS rev_2025
  UNION ALL SELECT 'Home', 18605.36, 23566.55
)
SELECT department, '2024' AS year, rev_2024 AS revenue FROM wide
UNION ALL
SELECT department, '2025', rev_2025 FROM wide
ORDER BY department, year

Other databases have shortcuts. SQL Server and BigQuery have PIVOT and UNPIVOT operators; PostgreSQL has crosstab() in the tablefunc extension. They still need the column list written out, so many analysts stick with CASE because it reads the same everywhere.

5. Worked example

Question: which return reasons are most common in stores and online?

  1. Join returns to orders to get the channel.
  2. Group by reason; one counting CASE per channel.
  3. Sort by the total.
-- try
SELECT r.reason,
       SUM(CASE WHEN o.channel = 'store' THEN 1 ELSE 0 END) AS store,
       SUM(CASE WHEN o.channel = 'online' THEN 1 ELSE 0 END) AS online,
       COUNT(*) AS total
FROM returns r
JOIN orders o ON o.order_id = r.order_id
GROUP BY r.reason
ORDER BY total DESC

“Wrong size” leads with 45 returns (33 store, 12 online). Online has fewer returns overall because it has fewer orders, so to compare channels fairly you would divide by each channel’s order count. Insight: “Sizing problems are our top return reason; better size charts could cut returns.”

6. Practice

-- exercise: Show the number of completed orders per channel, with one column for weekdays (Monday to Friday) and one for weekends (Saturday and Sunday).
-- hint: strftime('%w', order_date) gives '0' for Sunday up to '6' for Saturday. Use SUM(CASE WHEN ... IN ('0', '6') THEN 1 ELSE 0 END) for weekends.
SELECT channel, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY channel
-- answer:
SELECT channel,
       SUM(CASE WHEN strftime('%w', order_date) IN ('0', '6') THEN 0 ELSE 1 END) AS weekday_orders,
       SUM(CASE WHEN strftime('%w', order_date) IN ('0', '6') THEN 1 ELSE 0 END) AS weekend_orders
FROM orders
WHERE status = 'completed'
GROUP BY channel
-- exercise: Pivot the returns by year: one row per reason, with a column for 2024 returns and one for 2025 returns (use return_date).
-- hint: Group returns by reason and add SUM(CASE WHEN strftime('%Y', return_date) = '2024' THEN 1 ELSE 0 END), and the same for 2025.
SELECT reason, COUNT(*) AS returns
FROM returns
GROUP BY reason
-- answer:
SELECT reason,
       SUM(CASE WHEN strftime('%Y', return_date) = '2024' THEN 1 ELSE 0 END) AS returns_2024,
       SUM(CASE WHEN strftime('%Y', return_date) = '2025' THEN 1 ELSE 0 END) AS returns_2025
FROM returns
GROUP BY reason

Try it on your own data: reshape a CSV in [[sql-playground]], then chart the wide table with [[plot-maker]].

  • Databases prefer long tables; people prefer wide ones.
  • Pivot: GROUP BY the row label, and write one SUM(CASE WHEN … THEN x ELSE 0 END) per column.
  • To count instead of sum, let the CASE give 1 or 0.
  • Unpivot: one SELECT per column, stacked with UNION ALL.
  • CASE pivots work in every database; PIVOT operators exist but still need the column list.

New words: long table, wide table, pivot, unpivot, conditional aggregation.

Check yourself

1What does SUM(CASE WHEN year = '2025' THEN revenue ELSE 0 END) give in a GROUP BY department query?
2To count rows per category instead of summing a value, which works?
3What is the main drawback of pivoting with CASE?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.