How do you pivot rows into one column per status without a PIVOT operator?

## What "pivot" means here Pivoting reshapes a result: values that appear *down* a column become columns *across* the result. A table of `(customer_id, status, amount)` rows becomes one row per customer with a `new`, `shipped` and `cancelled` column. Some engines ship a dedicated operator for this (SQL Server and Oracle both have a `PIVOT` clause), but it is not portable, and every engine supports the hand-written form built from `GROUP BY` and conditional aggregates. ## The recipe Three decisions produce the query: 1. **What becomes a row?** That is the `GROUP BY` key. 2. **What becomes a column?** One conditional aggregate per distinct value you want to expose. 3. **What goes in a cell?** The expression in the `THEN` branch, and the aggregate that collapses it. ```sql SELECT customer_id, SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) AS new_cnt, SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_cnt, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_cnt FROM orders GROUP BY customer_id; ``` A monthly cross-tab is the same shape with a different key and a measure in the cell: ```sql SELECT product_id, SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN amount ELSE 0 END) AS jan, SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN amount ELSE 0 END) AS feb FROM orders WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01' GROUP BY product_id; ``` ## SUM versus MAX in the cell Use `SUM` when the cell is an additive measure — a count, a total. Use `MIN` or `MAX` when the cell holds at most one value per group and you simply want to lift it out. That is the classic attribute-table (entity-attribute-value) pivot: ```sql SELECT product_id, MAX(CASE WHEN attribute = 'color' THEN value END) AS color, MAX(CASE WHEN attribute = 'size' THEN value END) AS size, MAX(CASE WHEN attribute = 'material' THEN value END) AS material FROM product_attributes GROUP BY product_id; ``` Here the missing `ELSE` is deliberate. Every row that is not the colour row yields NULL, aggregates skip NULL, and `MAX` over the single surviving value returns it. `MAX` also works on text and dates, so this pattern is not limited to numbers. If two rows could supply a value for the same cell, `MAX` silently picks one — that is a data-quality decision you should make consciously rather than discover later. ## NULL cells versus zero cells With `ELSE 0`, an empty cell shows 0; without it, the cell is NULL. For counts and money, 0 is almost always the honest answer and keeps downstream arithmetic from propagating NULL. For an attribute lift, NULL is the honest answer — "this product has no recorded colour" is not the same statement as "its colour is the empty string". Choose per column, not per query. ## The structural limitation The output column list is part of the query text, so it is fixed at authoring time. Three consequences follow. First, a new status value silently vanishes from the report: rows still reach the group and still affect `COUNT(*)`, but no column exposes them. A defensive `COUNT(*) AS total` column lets a reader notice that the conditional columns no longer add up. Second, genuinely dynamic pivots — "a column per month present in the data" — cannot be expressed in a single static statement. You query the distinct values first, build the SQL text, and execute it, either in application code or in a procedural routine; both are outside portable declarative SQL. Third, wide pivots get unreadable fast. Twenty conditional aggregates in one `SELECT` list is a maintenance burden, and the reshaping is often better done in the reporting or application layer from a tidy `GROUP BY customer_id, status` result of three columns. Pivot in SQL when the consumer genuinely needs the wide shape — a spreadsheet export, a fixed dashboard grid — not reflexively. ## The opposite direction Unpivoting — turning wide columns back into rows — is not a conditional-aggregation problem at all; the portable form is a `UNION ALL` of one `SELECT` per source column, each projecting a literal label alongside the value.