Pivoting data with CASE WHEN

Pivoting with CASE WHEN rotates stacked rows into side-by-side columns, turning a tall list into a wide, scannable table.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Pivoting Retail Revenue with CASE WHEN

SELECT order_date,
       SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) AS electronics_revenue,
       SUM(CASE WHEN category = 'Clothing' THEN revenue ELSE 0 END) AS clothing_revenue,
       SUM(CASE WHEN category = 'Home' THEN revenue ELSE 0 END) AS home_revenue
FROM orders
GROUP BY order_date
ORDER BY order_date

Result:

order_date	electronics_revenue	clothing_revenue	home_revenue
2024-03-01	299.99	45.5	0
2024-03-02	189.0	0	75.25
2024-03-03	0	62.0	0

Step by step

One WHEN/THEN pair handles a single condition; rows that don't match get NULL (or the ELSE if present).
```
SELECT
  CASE
    WHEN category = 'Electronics' THEN revenue
    ELSE 0
  END AS category
FROM orders;
```

ELSE catches every row that didn't match any WHEN - gives you a default label.

SELECT
  CASE
    WHEN category = 'Electronics' THEN revenue
    ELSE 0
  END AS category
FROM orders;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query that labels each row 'high' when revenue is greater than 89.0, otherwise 'low'.

SELECT CASE WHEN revenue > 89.0 THEN 'high' ELSE 'low' END FROM orders

2. Using store_sales, write a query that labels each row 'high' when revenue is greater than 85.0, otherwise 'low'.

SELECT CASE WHEN revenue > 85.0 THEN 'high' ELSE 'low' END FROM store_sales

3. Using products, write a query that labels each row 'high' when price is greater than 22.5, otherwise 'low'.

SELECT CASE WHEN price > 22.5 THEN 'high' ELSE 'low' END FROM products

4. Using reviews, write a query that labels each row 'high' when rating is greater than 3.5, otherwise 'low'.

SELECT CASE WHEN rating > 3.5 THEN 'high' ELSE 'low' END FROM reviews

5. Using employees, write a query that labels each row 'high' when salary is greater than 54000, otherwise 'low'.

SELECT CASE WHEN salary > 54000 THEN 'high' ELSE 'low' END FROM employees

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed