SQL pivot and unpivot — reshaping without loss

Start with the basics

Reshaping moves data between wide and long: wide has one column per measure (a column each for act 1 and act 2), long has one row per measure with a label saying which.

Pivot turns rows into columns; unpivot turns columns into rows. You unpivot by stacking each column as its own labelled row, and pivot with a conditional sum per target column.

It reads like rearranging a spreadsheet, and mostly it is. The one rule that keeps it honest: every cell must carry a label so nothing collapses.

One that works

SELECT name, 'math' AS subject, math AS score FROM scores
UNION ALL
SELECT name, 'eng', eng FROM scores
ORDER BY subject;

Each column becomes its own labelled row — the subject label keeps the two cells distinct.

Pivot turns rows into columns and unpivot turns columns into rows — and it's only lossless if every cell keeps a label.

When to use it

• ·Reach for a pivot to turn categories into columns (a column per month), and unpivot to turn columns back into labelled rows.
• ·Keep a label column for every measure so no two cells merge.
• ·For a compute-across-columns task, unpivot, transform, then re-pivot.

That is exactly where reshaping loses data. Fold columns into rows without keeping a label for each, and two cells that happen to share a value collapse into one. Here is the day that bites.

See it happen

The unpivot that swallows a cell — UNION without a label

A puppet build has two acts, both 8 minutes. You unpivot the two minute columns into rows. Watch UNION merge the two identical 8s into one.

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

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

! 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 row.

The rest of the family

More ways reshaping loses data

Self-check

How to spot it in your own SQL

• →After unpivoting, the row count is lower than columns times rows.
• →You stacked columns with UNION instead of UNION ALL.
• →You need a pivot with a variable number of columns.
• →A wide-to-long-to-wide round-trip lost some values.

What people miss

Reshaping looks like moving cells around, but the trap is quiet data loss — a fold that drops a label, a round-trip that loses the grain. The shape looks right; a row just went missing.

Go deeper