Pivoting in SQL: Turning Rows Into Columns With CASE

Your manager wants a wide report; the data is tall. Skip the Excel export — four lines of SQL will do it.

The problem

Scores are stored tall: one row per subject. The report needs them wide: one column per subject.

The pivot formula

SELECT
  name,
  MAX(CASE WHEN subject = 'Maths'   THEN score END) AS maths,
  MAX(CASE WHEN subject = 'Physics' THEN score END) AS physics,
  MAX(CASE WHEN subject = 'Chem'    THEN score END) AS chem
FROM scores
GROUP BY name;

Three fixed parts:

1. GROUP BY the column that becomes the rows of the output
2. CASE WHEN picks the value belonging to each column
3. MAX or SUM collapses the group into a single row

Common mistakes

1. Forgetting the aggregate

-- Returns 4 rows, each with only one populated cell
SELECT name,
  CASE WHEN subject = 'Maths'   THEN score END AS maths,
  CASE WHEN subject = 'Physics' THEN score END AS physics
FROM scores;

CASE evaluates row by row, so it cannot collapse anything on its own. Wrap it in MAX and add GROUP BY.

2. Using MAX where SUM was meant

MAX is right when each cell holds one value. If a student sat Maths twice, MAX returns only the higher score — which may or may not be what you want. Use SUM to total them.

3. Adding ELSE 0 where it does not belong

-- A student who never sat Chemistry shows 0 rather than blank
MAX(CASE WHEN subject = 'Chem' THEN score ELSE 0 END)

For counts, ELSE 0 is correct. For values, leaving NULL is honest — scoring zero and not sitting the exam are different facts.

Unpivoting: columns back into rows

SELECT name, 'Maths'   AS subject, maths   AS score FROM wide_scores
UNION ALL
SELECT name, 'Physics', physics FROM wide_scores
UNION ALL
SELECT name, 'Chem',    chem    FROM wide_scores;

This is LeetCode 1795 — a shape that comes up constantly in analytics interviews.

Practice

• LeetCode 1179 — Reformat Department Table
• LeetCode 1795 — Rearrange Products Table
• LeetCode 1873 — Calculate Special Bonus
• HackerRank — Occupations (a hard pivot, worth doing)