CASE, Pivoting and Unpivoting

CASE is SQL's if/else, and it is more load-bearing than it looks: combined with an aggregate it produces pivots, bucketed reports and multi-metric summaries in a single pass, using nothing vendor-specific.

The two forms

-- Searched CASE: any condition
SELECT first_name, salary,
       CASE
           WHEN salary >= 100000 THEN 'Senior'
           WHEN salary >= 60000  THEN 'Mid'
           ELSE 'Junior'
       END AS band
FROM employees;

-- Simple CASE: equality against one expression
SELECT CASE department
           WHEN 'ENG'   THEN 'Engineering'
           WHEN 'SALES' THEN 'Sales'
           ELSE 'Other'
       END AS department_name
FROM employees;

Two rules worth internalising:

• Conditions are evaluated top to bottom and the first match wins. So order matters:

putting >= 60000 before >= 100000 labels everyone Mid, and the query is still valid.

• No `ELSE` means `NULL` for non-matching rows. That is often exactly what you want —

see conditional aggregation below — but it is a silent default.

CASE is an expression, so it works anywhere a value does: SELECT, WHERE, `ORDER BY, GROUP BY`, and inside aggregates.

Custom sort order

SELECT * FROM tasks
ORDER BY CASE priority
             WHEN 'critical' THEN 1
             WHEN 'high'     THEN 2
             WHEN 'medium'   THEN 3
             ELSE 4
         END,
         created_at DESC;

Alphabetical order on a status column is almost never the meaningful order, and this is the portable fix.

Conditional aggregation

The trick that makes CASE essential rather than convenient. Because `COUNT` ignores NULL, a CASE with no ELSE counts only the matching rows:

SELECT
    department,
    COUNT(*)                                        AS headcount,
    COUNT(CASE WHEN salary > 100000 THEN 1 END)     AS high_earners,
    SUM(CASE WHEN is_active THEN 1 ELSE 0 END)      AS active,
    AVG(CASE WHEN tenure_years > 5 THEN salary END) AS avg_veteran_salary
FROM employees
GROUP BY department;

Four different filtered metrics, one scan. The alternative — four separate queries UNIONed or joined — reads worse and costs more.

Note the asymmetry: COUNT needs no ELSE (NULLs are skipped), SUM needs ELSE 0 (NULLs would make the whole sum NULL only if every row were NULL, but the intent is clearer with the zero), and AVG deliberately omits ELSE so non-matching rows are excluded from the denominator rather than dragging it toward zero.

Pivoting: rows into columns

BEFORE                          AFTER
department  month  revenue      department  jan     feb     mar
Sales       Jan    100          Sales       100     150     120
Sales       Feb    150          Eng          80      90     110
Sales       Mar    120

SELECT
    department,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar
FROM monthly_revenue
GROUP BY department;

That is the whole technique: **one aggregate per output column, each filtered by a CASE.** It works on every engine. SQL Server and Oracle have a PIVOT operator and PostgreSQL has crosstab() in the tablefunc extension, but the CASE form is what to write in an interview because it needs no dialect footnote.

The unavoidable limitation, and the expected follow-up: **the columns must be known when the query is written.** SQL results have a fixed shape, so a pivot over a value set that changes at run time requires generating the SQL text dynamically in the application or a stored procedure.

Unpivoting: columns into rows

The reverse, usually to normalise a wide table:

SELECT department, 'Jan' AS month, jan AS revenue FROM wide_revenue
UNION ALL
SELECT department, 'Feb', feb FROM wide_revenue
UNION ALL
SELECT department, 'Mar', mar FROM wide_revenue;

Portable and verbose. PostgreSQL can do it in one pass with LATERAL (VALUES ('Jan', jan), ('Feb', feb), ('Mar', mar)) AS t(month, revenue), and SQL Server has UNPIVOT; both are worth naming as the tidier options.

Note UNION ALL, not UNION — see set operations. Deduplicating here would silently merge two departments with identical figures.

COALESCE, NULLIF and IIF

Shorthands built on the same idea:

COALESCE(commission, 0)            -- first non-NULL argument
NULLIF(total, 0)                   -- NULL if the two are equal — guards ÷0
IIF(salary > 50000, 'High', 'Low') -- SQL Server / SQLite two-branch CASE

NULLIF in a denominator is the standard division-by-zero guard: revenue / NULLIF(orders, 0) yields NULL instead of erroring. More in NULL handling.

Mistakes that cost the interview

• Condition order in a searched `CASE`. The first match wins.
• Assuming a missing `ELSE` yields 0. It yields NULL.
• `ELSE 0` inside `AVG`, which drags the average toward zero instead of excluding the

row.

• `UNION` instead of `UNION ALL` when unpivoting.
• Promising a dynamic pivot. Say the columns must be fixed, and where dynamic SQL would

go.

Practise these on the SQL sheet.