CASE for Pivoting and Unpivoting Data
What you'll learn: How to transform rows into columns (pivoting) using CASE expressions combined with aggregation to create summary tables from your query results.
What is Pivoting with CASE?
Pivoting transforms row-based data into column-based data—think of rotating your spreadsheet 90 degrees. Instead of seeing multiple rows for each category, you see one row with categories spread across columns. CASE expressions make this possible by conditionally selecting values based on a criterion, then using aggregate functions to collapse those values into a single row per group.
The Core Pattern
The technique follows this structure:
SELECT
grouping_column,
SUM(CASE WHEN category = 'A' THEN value ELSE 0 END) AS category_a,
SUM(CASE WHEN category = 'B' THEN value ELSE 0 END) AS category_b,
SUM(CASE WHEN category = 'C' THEN value ELSE 0 END) AS category_c
FROM table_name
GROUP BY grouping_column;
Each CASE expression creates a "virtual column" that isolates values for one category. The aggregate function (like SUM, COUNT, or MAX) then collapses multiple rows into one result per group.
Practical Example
Transform sales data from rows to columns:
SELECT
employee_name,
SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS q1_sales,
SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS q2_sales,
SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS q3_sales,
SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS q4_sales
FROM sales_data
GROUP BY employee_name;
This transforms vertical quarterly sales rows into horizontal columns, making comparisons easier.
Key Takeaway: Use CASE inside aggregate functions with GROUP BY to pivot row data into columns—each CASE condition becomes a new column, and aggregation collapses the results into a summarized view.