Reshaping — pivot & unpivot

Conditional-aggregation pivot, UNION-ALL/lateral unpivot, wide-vs-long as a modeling choice, dynamic-column limits.

• ★Unpivot via UNION ALL / lateral

  Unpivot with UNION ALL (or lateral VALUES) — then decide whether absent measures become NULL rows or are dropped.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE builds(puppet VARCHAR, act1_mins INT, act2_mins INT); INSERT INTO builds VALUES ('alpha',8,8),('beta',5,9);

  The trap

  SELECT puppet, act1_mins AS mins FROM builds UNION SELECT puppet, act2_mins FROM builds ORDER BY puppet, mins;

  alpha	8
  beta	5
  beta	9

  The fix

  SELECT puppet, 'act1' AS act, act1_mins AS mins FROM builds UNION ALL SELECT puppet, 'act2', act2_mins FROM builds ORDER BY puppet, act;

  alpha	act1	8
  alpha	act2	8
  beta	act1	5
  beta	act2	9

  The unpivot used UNION and dropped the act label, so alpha's two identical 8-minute acts collapsed into one row — UNION ALL with a label keeps every cell as its own long-form row.

• Pivot via conditional aggregation

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

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE cs(scent VARCHAR, month VARCHAR, n INT); INSERT INTO cs VALUES ('rose','Jan',5),('rose','Feb',3),('pine','Jan',2);

  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;

  pine	2	NULL
  rose	5	3

  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;

  pine	2	0
  rose	5	3

  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.

• Dynamic column limits

  SQL can't parameterize identifiers — a truly dynamic pivot needs generated SQL or the app layer.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE tiles(project VARCHAR, color VARCHAR, n INT); INSERT INTO tiles VALUES ('m','red',3),('m','blue',2),('m','green',4);

  The trap

  SELECT project, SUM(CASE WHEN color='red' THEN n ELSE 0 END) AS red, SUM(CASE WHEN color='blue' THEN n ELSE 0 END) AS blue FROM tiles GROUP BY project ORDER BY project;

  m

  3

  2

  The fix

  SELECT project, color, SUM(n) AS n FROM tiles GROUP BY project, color ORDER BY project, color;

  m	blue	2
  m	green	4
  m	red	3

  The fixed pivot named red and blue, so the green tiles vanished — SQL result columns are fixed at compile time, so a column-per-value pivot can't grow with the data; long form (one row per color) handles any number.

• Wide-long round-trip preserves grain

  Wide<->long is lossless only if the grain survives the trip — unpivot, transform, re-pivot is the honest way to compute across columns.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE fleet(canoe VARCHAR, hull_hours INT, patch_count INT); INSERT INTO fleet VALUES ('c1',20,3),('c2',10,8);

  The trap

  SELECT canoe, CASE WHEN hull_hours >= patch_count THEN 'hull_hours' ELSE 'patch_count' END AS metric FROM fleet ORDER BY canoe;

  c1	hull_hours
  c2	hull_hours

  The fix

  WITH long AS (SELECT canoe,'hull_hours' AS metric, hull_hours AS v FROM fleet UNION ALL SELECT canoe,'patch_count',patch_count FROM fleet), dev AS (SELECT canoe, metric, v - AVG(v) OVER (PARTITION BY metric) AS above FROM long) SELECT canoe, metric FROM dev QUALIFY ROW_NUMBER() OVER (PARTITION BY canoe ORDER BY above DESC) = 1 ORDER BY canoe;

  c1	hull_hours
  c2	patch_count

  The cross-metric answer came from wide form — comparing raw hull_hours against patch_count picked hull for c2, where the long-form route (deviation from each metric's fleet average) correctly picks patch_count.