Advanced SQL

Pivot, Unpivot, and Conditional Aggregation

Pivoting turns category values into columns and unpivoting turns columns into rows; conditional aggregation is the portable, explicit core, while vendor operators have distinct static-column and null behavior.

Conditional aggregation expresses pivot semantics portably.
A pivot requires a duplicate policy.
Static pivot syntax needs a known output column list.
Null and zero are different results.
Unpivot is not generally an inverse.

Portable quarterly revenue for an explicit year

SELECT o.customer_id,
       SUM(CASE WHEN o.placed_at >= :year_start
                 AND o.placed_at < :q2_start THEN i.quantity * i.unit_price ELSE 0 END) AS q1,
       SUM(CASE WHEN o.placed_at >= :q2_start
                 AND o.placed_at < :q3_start THEN i.quantity * i.unit_price ELSE 0 END) AS q2,
       SUM(CASE WHEN o.placed_at >= :q3_start
                 AND o.placed_at < :q4_start THEN i.quantity * i.unit_price ELSE 0 END) AS q3,
       SUM(CASE WHEN o.placed_at >= :q4_start
                 AND o.placed_at < :next_year_start THEN i.quantity * i.unit_price ELSE 0 END) AS q4
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'PAID'
  AND o.placed_at >= :year_start
  AND o.placed_at < :next_year_start
GROUP BY o.customer_id;

WITH order_quarters AS (
  SELECT o.customer_id, EXTRACT(QUARTER FROM o.placed_at) AS quarter_no,
         i.quantity * i.unit_price AS line_total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'PAID'
    AND o.placed_at >= :year_start AND o.placed_at < :next_year_start
)
SELECT customer_id,
       SUM(line_total) FILTER (WHERE quarter_no = 1) AS q1,
       SUM(line_total) FILTER (WHERE quarter_no = 2) AS q2
FROM order_quarters GROUP BY customer_id;

Technique trade-offs
Technique	Strength	Constraint
Conditional aggregation	Portable and explicit	One expression per output column
PostgreSQL aggregate `FILTER`	Concise conditional aggregate	PostgreSQL-specific syntax
SQL Server `PIVOT`	Dedicated row-to-column operator	Static `IN` column list
SQL Server `UNPIVOT`	Dedicated column-to-row operator	Null/aggregation loss prevents general inversion