SQL Intermediate Lesson 8: Pivot Reports With CASE

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 8 of 10

Managers want reports with categories across the top. The portable way to pivot in SQL is conditional aggregation: a SUM of a CASE per column.

SELECT c.region,
       SUM(CASE WHEN o.status = 'Delivered' THEN o.qty * p.price ELSE 0 END) AS delivered,
       SUM(CASE WHEN o.status = 'Shipped'   THEN o.qty * p.price ELSE 0 END) AS shipped,
       SUM(CASE WHEN o.status = 'Pending'   THEN o.qty * p.price ELSE 0 END) AS pending
FROM orders o JOIN customers c USING (customer_id) JOIN products p USING (product_id)
GROUP BY c.region;

For counts, sum 1s: SUM(CASE WHEN ... THEN 1 ELSE 0 END). In SQLite, MySQL and PostgreSQL a true condition is 1, so SUM(mode = 'UPI') works too.

Actual vs target

WITH a AS (SELECT region, SUM(amount) AS actual FROM sales WHERE month = '2026-09' GROUP BY region)
SELECT t.region, a.actual, t.target, ROUND(100.0 * a.actual / t.target, 1) AS pct
FROM targets t LEFT JOIN a ON a.region = t.region
WHERE t.month = '2026-09';

Common mistakes

• Missing ELSE 0: a column with no matches shows NULL, and adding NULLs across gives NULL.
• Hard-coding months that roll every year: generate the column list or pivot in the reporting tool.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.