Pivot Tables in SQL
Rows into columns.
Helen loves the pivot tables in her spreadsheet. Countries down the side, order statuses across the top, and a count in every cell. Group by gives the same numbers, but as a long list. Let's turn that list into a neat grid.

- Spreadsheet pivot tables
- Countries down, statuses across
- A neat grid
The long version
Here's group by, with two columns. Run it. It's correct, but it's long. One row for every country and status. We're only showing six, but there are twenty four in total. Hard to compare at a glance.
SELECT c.country, o.status, COUNT(*) AS orders
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.country, o.status
LIMIT 6;โโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโ โ country โ status โ orders โ โโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโค โ Australia โ cancelled โ 30 โ โ Australia โ delivered โ 233 โ โ Australia โ returned โ 7 โ โ Australia โ shipped โ 27 โ โ Canada โ cancelled โ 26 โ โ Canada โ delivered โ 215 โ โโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโ
The pivot
Here's the trick you learned earlier. Case inside sum, once for each status. Each case makes its own column. Run it. One row per country, and four status columns. Now it's easy to compare. The UK had the most returns, with sixteen.
SELECT c.country,
SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
SUM(CASE WHEN o.status = 'returned' THEN 1 ELSE 0 END) AS returned
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.country;โโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโ โ country โ delivered โ shipped โ cancelled โ returned โ โโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโค โ Australia โ 233 โ 27 โ 30 โ 7 โ โ Canada โ 215 โ 18 โ 26 โ 13 โ โ Germany โ 110 โ 8 โ 12 โ 10 โ โ Ireland โ 128 โ 17 โ 13 โ 7 โ โ UK โ 274 โ 29 โ 31 โ 16 โ โ USA โ 305 โ 31 โ 28 โ 10 โ โโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโ
Comparing years
Pivots are perfect for comparing years side by side. Here's revenue per genre, with twenty twenty four and twenty twenty five as columns. Run it. Every genre grew. Fantasy more than doubled, from about two thousand two hundred, to four thousand seven hundred.
SELECT b.genre,
ROUND(SUM(CASE WHEN o.order_date < '2025-01-01'
THEN oi.quantity * b.price END), 0) AS rev_2024,
ROUND(SUM(CASE WHEN o.order_date >= '2025-01-01'
THEN oi.quantity * b.price END), 0) AS rev_2025
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY b.genre
ORDER BY rev_2025 DESC
LIMIT 5;โโโโโโโโโโโโโโฌโโโโโโโโโโโฌโโโโโโโโโโโ โ genre โ rev_2024 โ rev_2025 โ โโโโโโโโโโโโโโผโโโโโโโโโโโผโโโโโโโโโโโค โ Technology โ 3036.0 โ 5574.0 โ โ Fantasy โ 2270.0 โ 4711.0 โ โ Mystery โ 2679.0 โ 3893.0 โ โ Thriller โ 1959.0 โ 3716.0 โ โ Business โ 1383.0 โ 2927.0 โ โโโโโโโโโโโโโโดโโโโโโโโโโโดโโโโโโโโโโโ
Summary
Case inside sum turns rows into columns. That's a pivot table in SQL. Next, putting a whole list into one cell.
Key takeaways- One CASE per column
- SUM or COUNT
- Easy to compare