AI Hints
Homeโ€บSQL Tutorialโ€บPivots, Lists, and Recursion
SQL ยท Lesson 108 of 126

Pivot Tables in SQL

Rows into columns.

โฑ 2 min readโ–ถ Video at 2:32:43๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Pivot Tables in SQL
From the video: Pivot Tables in SQL
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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
Next up: Lists in One Cell โ€” GROUP_CONCAT