Reshaping — pivot & unpivot
Concept  ·  SQL & Querying

Portable pivots are SUM(CASE WHEN col=x ...); PIVOT syntax is a dialect convenience over the same idea.

There is no magic in a pivot: columns are aggregates with per-column filters, which is why it's portable everywhere CASE is. The consequences fall out for free: the column set is fixed at write time (12 months fine, arbitrary products not — the dynamic-columns wall); the empty-cell value is yours to choose and the choice is case_missing_else verbatim; and wide outputs are for reading — feeding a pivot into more SQL usually means you wanted the long form.

See it break
cs
scentmonthn
roseJan5
roseFeb3
pineJan2
The trap
SELECT
  scent,
  SUM(
    CASE
      WHEN month = 'Jan' THEN n
      END
  ) AS jan,
  SUM(
    CASE
      WHEN month = 'Feb' THEN n
      END
  ) AS feb
FROM
  cs
GROUP BY
  scent
ORDER BY
  scent;
The fix
SELECT
  scent,
  SUM(
    CASE
      WHEN month = 'Jan' THEN n
      ELSE 0
    END
  ) AS jan,
  SUM(
    CASE
      WHEN month = 'Feb' THEN n
      ELSE 0
    END
  ) AS feb
FROM
  cs
GROUP BY
  scent
ORDER BY
  scent;
INPUTYour queryThe fix
pine2NULL0
rose533

The never-sold cell read NULL because its CASE had no ELSE — a pivot is one SUM(CASE) per column, and whether that empty cell reads 0 is a decision that wasn't made.

The rule

Conditional aggregation (portable), the FILTER (WHERE …) spelling, or native PIVOT as dialect garnish.

Shows up elsewhere
Conditional aggregationUnpivot via UNION ALL / lateral
Try one →Prove it in practice →