Conditional Aggregation, Pivot and Unpivot

Concept

A pivot turns distinct values of a column into columns of the result. Reports want it constantly - months across the top, categories down the side. Standard SQL has no portable PIVOT, but conditional aggregation does the same job in every dialect.

Syntax

SELECT group_column,
       SUM(CASE WHEN pivot_column = 'value1' THEN measure ELSE 0 END) AS value1,
       SUM(CASE WHEN pivot_column = 'value2' THEN measure ELSE 0 END) AS value2
FROM   table_name
GROUP BY group_column;

Example: orders by status per customer

SELECT c.name AS customer,
       COUNT(*)                                                  AS total_orders,
       COUNT(CASE WHEN o.status = 'shipped'   THEN 1 END)        AS shipped,
       COUNT(CASE WHEN o.status = 'pending'   THEN 1 END)        AS pending,
       COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END)        AS cancelled,
       SUM(CASE WHEN o.status = 'shipped' THEN o.total ELSE 0 END) AS shipped_value
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total_orders DESC;

Example: months across the top

SELECT EXTRACT(YEAR FROM order_date) AS yr,
       SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN total ELSE 0 END) AS jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN total ELSE 0 END) AS feb,
       SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN total ELSE 0 END) AS mar,
       SUM(total)                                                              AS year_total
FROM   orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY yr;

Explanation

Every pivot has three parts, and naming them makes any pivot easy to write:

• The row key - what goes in GROUP BY. Here, the customer or the year.
• The pivot column - whose values become columns. Here, status or month.
• The measure - what is aggregated into each cell. Here, a count or a sum.

Remember the rule from the aggregates note: COUNT(CASE WHEN ... THEN 1 END) deliberately has no ELSE, because COUNT ignores NULL. Adding ELSE 0 would count every row.

The PIVOT operator, where it exists

-- SQL Server and Oracle only
SELECT customer, [shipped], [pending], [cancelled]
FROM  (SELECT c.name AS customer, o.status, o.id FROM orders o JOIN customers c ON c.id = o.customer_id) src
PIVOT (COUNT(id) FOR status IN ([shipped], [pending], [cancelled])) AS p;

MySQL, MariaDB, PostgreSQL and SQLite have no PIVOT. Conditional aggregation is the portable answer, and it is arguably clearer anyway because the aggregate for each column is written out.

Unpivot: columns back into rows

-- A wide table with q1..q4 columns, turned into one row per quarter
SELECT dept_id, 'Q1' AS quarter, q1_spend AS spend FROM budgets
UNION ALL
SELECT dept_id, 'Q2', q2_spend FROM budgets
UNION ALL
SELECT dept_id, 'Q3', q3_spend FROM budgets
UNION ALL
SELECT dept_id, 'Q4', q4_spend FROM budgets;

Unpivoting is usually a sign the source table is not in first normal form - q1_spend through q4_spend is a repeating group. If you control the schema, store one row per department per quarter instead, and pivot on the way out.

Important rules

• The pivot column values must be known when you write the query. SQL cannot generate columns from data at run time without dynamic SQL.
• Use SUM(CASE ... ELSE 0 END) for measures and COUNT(CASE ... THEN 1 END) for counts.
• Conditional aggregation makes one pass over the data, however many columns you produce.
• PIVOT exists only in SQL Server and Oracle.

Common mistakes

• Adding ELSE 0 inside COUNT(CASE ...) and getting the total row count in every column.
• Running one query per column and joining the results, instead of one grouped query.
• Expecting the column list to expand automatically when a new status appears.
• Storing pivoted data in the base table, creating a repeating group.

Best practices

• Store data long and narrow; pivot in the query or the reporting layer.
• Name the row key, pivot column and measure before writing the SQL.
• Add a total column and a total row so the report can be sanity checked.
• Where the pivot column values genuinely change, build the query in the application rather than reaching for dynamic SQL in the database.

Practice

1. Pivot products so each category is a column with the count of products in it.
2. Produce revenue per country with one column per order status.
3. Explain why the columns of a pivot cannot be driven by the data itself.