Part 2 — Querying › Chapter 16

CASE, COALESCE and conditional aggregation

How to pivot without PIVOT, and why conditional aggregation beats five self-joins every single time.

From the advert

“Reporting con SSRS e Power BI” — nine tenths of report work is turning rows into columns and putting a label on a number. That is this chapter.

CASE: the only conditional you need

Both spellings of CASE, and the one thing the short form cannot do.

-- SEARCHED CASE: any predicate, evaluated top to bottom, FIRST match wins.
SELECT id,                                                 -- the row's key
       CASE WHEN total_cents >= 100000 THEN 'large'        -- ≥ €1000 → 'large'
            WHEN total_cents >=  20000 THEN 'medium'       -- order matters: this is reached
            ELSE 'small'                                   -- …only when the first test failed
       END AS bucket                                       -- omit ELSE and non-matches are NULL
FROM orders;                                               -- one bucket per order

-- SIMPLE CASE: equality against ONE expression. Compact, and cannot test NULL.
CASE status WHEN 'draft'   THEN 'Bozza'                    -- status = 'draft'
            WHEN 'shipped' THEN 'Spedito'                  -- status = 'shipped'
            ELSE status END                                -- anything else, untranslated

Types, order and the NULL limitation

CASE is an expression: it produces a value, so it can sit in SELECT, WHERE, ORDER BY, inside an aggregate, or in a CHECK constraint. It is not an IF statement.
Every branch must return a compatible type and the result takes the widest of them. A CASE with a numeric branch and a text branch either converts silently or fails, depending on the engine — decide the output type yourself with a CAST.
Branch order is significant. Swap the two WHENs above and every large order is labelled “medium”, because 100000 is also ≥ 20000.
CASE status WHEN NULL THEN … can never match: the simple form compares with =, and nothing equals NULL. Use the searched form with WHEN status IS NULL.

⚠ Trap

Order matters and there is no warning. Put WHEN total >= 20000 before WHEN total >= 100000 and every large order is labelled medium — the query runs, returns rows, and the report is quietly wrong. Write the branches from the most specific downwards, and always include an ELSE so an unhandled value is visible rather than a null nobody looks at.

Pivot without PIVOT

🧒 Simple

Your data is a long list: “January, Modena, 40”. The boss wants a grid with months down the side and cities across the top. Turning the list into the grid is a pivot — and the trick is just to make one column per city that only adds up the numbers belonging to that city.

A pivot with no PIVOT keyword: one column per city, each summing only its own rows.

-- Portable everywhere, readable, and the answer to give in an interview.
SELECT                                                           -- the report columns follow
  DATE_TRUNC('month', o.placed_at)                                    AS month,   -- the row axis
  SUM(CASE WHEN c.city = 'Modena'  THEN o.total_cents ELSE 0 END)/100 AS modena,  -- column 1
  SUM(CASE WHEN c.city = 'Parma'   THEN o.total_cents ELSE 0 END)/100 AS parma,   -- column 2
  SUM(CASE WHEN c.city = 'Bologna' THEN o.total_cents ELSE 0 END)/100 AS bologna, -- column 3
  SUM(o.total_cents)/100                                              AS total    -- the row total
FROM orders o                                                                     -- the facts
JOIN customers c ON c.id = o.customer_id                                          -- for the city
GROUP BY DATE_TRUNC('month', o.placed_at)                                         -- one row per month
ORDER BY month;                                                                   -- chronological

The mechanism, and the integer division to notice

Each column is an aggregate over a CASE that returns the amount for its own city and 0 for everything else. Summing that gives the city's total — a pivot, built from parts you already know.
ELSE 0 is right inside SUM here: a month with no Modena orders should read 0, not blank. Inside COUNT the opposite holds — there, ELSE NULL is what makes the count skip non-matches.
/100 is integer division: €89.99 arrives as 89. Write /100.0 when the cents matter — this example rounds down deliberately, and that is exactly the kind of decision worth stating out loud in a review.
The column list is fixed at write time. A new city needs a new line, which is the honest limitation of pivoting in SQL: genuinely dynamic columns need dynamic SQL (chapter 34) or a client that can do it.

🎓 Why not the PIVOT operator?

SQL Server and Oracle have a PIVOT keyword. It is shorter for simple cases and it is not portable, it only handles one aggregate, and it still needs the column list written out — so it solves none of the real problem. Conditional aggregation works in every engine, supports several different aggregates side by side, and is easier to read. Know that PIVOT exists; reach for CASE.

The column list must be static

SQL returns a fixed set of columns decided at parse time, so a pivot over “whatever cities exist” requires dynamic SQL — building the statement from the data and executing it. That is a real technique with a real injection risk, and it is usually the wrong answer: let the reporting tool pivot. Power BI, SSRS and Excel all pivot a long result natively, and returning long data keeps the query static, cacheable and safe. Saying this is a better answer than writing the dynamic SQL.

Unpivot: grid back into list

You will meet this cleaning up a spreadsheet import, where somebody has columns jan, feb, mar…:

Unpivot: turning twelve month columns back into twelve rows.

-- Portable: one branch per source column, stapled together with UNION ALL.
SELECT product_id, 1 AS month, jan AS qty FROM sales_wide WHERE jan IS NOT NULL  -- January
UNION ALL SELECT product_id, 2, feb FROM sales_wide WHERE feb IS NOT NULL        -- February
UNION ALL SELECT product_id, 3, mar FROM sales_wide WHERE mar IS NOT NULL;       -- March, …

-- PostgreSQL: more compact, and ONE pass over the table instead of twelve.
SELECT product_id, m.month, m.qty                                  -- the long-format columns
FROM sales_wide                                                    -- read the wide table once…
CROSS JOIN LATERAL (VALUES (1, jan), (2, feb), (3, mar))           -- …and expand each row…
     AS m(month, qty)                                              -- …naming the two new columns
WHERE m.qty IS NOT NULL;                                           -- drop the empty months

Why the second form is worth learning

The UNION ALL version reads sales_wide once per month — twelve scans for a twelve-column table. The LATERAL (VALUES …) version reads it once and expands each row in place.
VALUES here is an inline table: rows written literally. The alias m(month, qty) names both the table and its columns, which is required.
SQL Server has UNPIVOT, and also supports CROSS APPLY (VALUES …), which is the same idea with different keywords.
Every branch must produce the same column types. If jan is INTEGER and feb is DECIMAL, the result takes the wider type — check it, rather than discovering it in a report.

COALESCE and NULLIF in reports

COALESCE and NULLIF doing the three jobs they exist for in reports.

SELECT                                                       -- the report columns follow
  -- A display name, from the first source that has one:
  COALESCE(c.company_name,                                   -- prefer the company…
           c.first_name || ' ' || c.last_name,               -- …then the person's full name…
           'Unknown')                        AS display_name, -- …then admit we do not know
  -- Division that cannot fail, and returns NULL rather than a fake zero:
  ROUND(100.0 * o.delivered                                  -- 100.0 → REAL, so no truncation
        / NULLIF(o.total, 0), 1)             AS delivery_pct, -- 0 → NULL → the division is NULL
  -- Treat the empty string as missing (the classic import artefact):
  COALESCE(NULLIF(TRIM(c.vat_number), ''), '—') AS vat        -- trim, blank→NULL, NULL→a dash
FROM customers c ...                                          -- (joined to the counts above)

Three idioms worth memorising

COALESCE(a, b, c) — first non-NULL wins, and it short-circuits. Note the second argument: if first_name is NULL, || makes the whole concatenation NULL and COALESCE falls through to 'Unknown'. That is the behaviour you want here, and it is worth knowing it is happening.
NULLIF(x, 0) — the division guard. No CASE, no exception, and the report says “undefined” instead of lying with a zero.
NULLIF(TRIM(x), '') — the import cleaner. Whitespace-only becomes empty becomes NULL, and then COALESCE supplies the placeholder. Three functions, one honest column.
Beware SQL Server's ISNULL as a substitute: its result takes the first argument's type, so ISNULL(varchar5_col, 'unknown') silently returns 'unkno'. COALESCE is standard and takes the widest type.

⚠ Trap — COALESCE vs ISNULL in T-SQL

ISNULL(a, b) returns the type of a, so ISNULL(short_col, 'a long default string') silently truncates the default. COALESCE uses type precedence across all arguments and does not. COALESCE is also standard SQL. Use it.

The reporting shape you will be asked for

The archetypal reporting query: counts, a percentage and revenue, in one scan.

-- "Monthly order status breakdown with percentages" — the query behind half the
-- dashboards you will ever be asked to write. One scan, no subqueries.
SELECT                                                           -- the report columns follow
  strftime('%Y-%m', placed_at)                             AS month,      -- '2026-03', TEXT
  COUNT(*)                                                 AS orders,     -- rows in the month
  COUNT(CASE WHEN status = 'delivered' THEN 1 END)         AS delivered,  -- non-matches → NULL
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END)         AS cancelled,  -- …so they are skipped
  ROUND(100.0 * COUNT(CASE WHEN status = 'cancelled' THEN 1 END)         -- the same count again…
        / NULLIF(COUNT(*), 0), 1)                          AS cancel_pct, -- …over the total, guarded
  SUM(CASE WHEN status <> 'cancelled'                                     -- revenue excludes…
           THEN total_cents ELSE 0 END) / 100.0            AS revenue_eur -- …cancellations
FROM orders                                                               -- one table
GROUP BY month                                                            -- one row per month
ORDER BY month;                                                           -- chronological

Everything that could go wrong here, and does not

NULLIF(COUNT(*), 0) — COUNT(*) cannot be zero inside a group that exists, so this guard is defensive rather than necessary. Leave it: the day this query grows a HAVING or a filtered count in the denominator, it stops being unnecessary.
100.0 * before the division, not after — with integers, COUNT(…) / COUNT(*) is 0 for everything, and multiplying 0 by 100 is still 0.
/ 100.0 on the revenue keeps the cents; / 100 would truncate every month's total to whole euros.
<> 'cancelled' would drop rows whose status is NULL — safe here only because the column is NOT NULL by constraint. That is the constraint paying for itself.

CASE branches are evaluated in order and the first match wins. Order them most specific first.
Always write an ELSE. A missing one produces nulls that nobody investigates.
Simple CASE cannot match NULL, because nothing equals NULL.
Pivot with conditional aggregation: portable, multi-aggregate, readable. PIVOT is shorter and non-portable.
The column list of a pivot is fixed at parse time. A dynamic pivot needs dynamic SQL — usually let the reporting tool do it instead.
Unpivot with UNION ALL, or a lateral VALUES list for one pass.
NULLIF(x, 0) is the division guard; NULLIF(TRIM(x), '') normalises empty strings to missing.
Prefer COALESCE to ISNULL: standard, n-ary, and it will not truncate your default.

Interview questions this chapter answers

Pivot monthly sales into one column per region.

Conditional aggregation — one SUM(CASE WHEN region = 'x' THEN amount ELSE 0 END) per region, grouped by month. I would mention that the region list has to be static, and that if it is genuinely dynamic the right answer is usually to return long data and let the reporting layer pivot it.

Difference between COALESCE and ISNULL?

COALESCE is standard, takes any number of arguments, and determines the result type from all of them. ISNULL is T-SQL, takes two, and returns the first argument's type — so it can silently truncate the replacement value.

How do you avoid divide-by-zero in a percentage column?

NULLIF(denominator, 0). Dividing by null yields null, which is the honest answer for “undefined”, and no error is raised. Wrapping it in COALESCE(…, 0) is a separate decision about how to display it.