0Pricing
SQL Interview Prep · Lesson

Unpivoting Columns Into Rows

Reversing wide tables with UNPIVOT or UNION ALL.

Unpivoting Columns Into Rows is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Reverse Problem

Unpivoting is the mirror image of pivoting: you take a wide table and turn its columns back into rows. Interviewers ask this when data arrives in spreadsheet shape but needs to be normalized for analysis.

Example: a table with columns q1, q2, q3, q4 per region must become rows of (region, quarter, amount). This long form is what aggregation, joining, and charting all prefer.

-- Wide input we want to unpivot
region | q1  | q2  | q3  | q4
-------+-----+-----+-----+----
East   | 100 | 150 | 120 | 180
West   | 200 | 250 | 210 | 260

The Portable UNION ALL Pattern

The dialect-independent answer is UNION ALL: write one SELECT per source column, each emitting a literal label and that column's value.

Use UNION ALL, not UNION, so you do not pay for deduplication and you keep every row, even when two cells share a value.

SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;

Why UNION ALL Not UNION

This is a classic interview trap. UNION removes duplicate rows across the whole result. If East and West both had 100 for Q1, plain UNION would collapse identical rows and you would lose data.

UNION ALL concatenates without deduping, which is what unpivoting needs. It is also faster because no sort or hash for dedup is required.

-- UNION would wrongly merge identical (region, quarter, amount) rows
-- UNION ALL keeps every row, always the correct choice here

Column Type Alignment

Every branch of the UNION ALL must produce the same number of columns with compatible types in the same order. The column names come from the first SELECT.

If your wide columns differ in type (say one is int and another decimal), the engine picks a common type. If they are truly incompatible, cast explicitly so the union does not fail.

SELECT region, 'revenue' AS metric, CAST(revenue AS decimal(12,2)) AS val FROM t
UNION ALL
SELECT region, 'units',   CAST(units   AS decimal(12,2))        FROM t;

SQL Server UNPIVOT

SQL Server has a dedicated UNPIVOT operator that is more concise than UNION ALL. You name the new value column, the new label column, and list the source columns to fold.

One important behavior: UNPIVOT drops rows where the value is NULL. Interviewers test whether you know this side effect.

SELECT region, quarter, amount
FROM wide_sales
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
) AS u;

UNPIVOT Drops NULLs

If a region has NULL in q3, SQL Server's UNPIVOT simply omits that row from the output. If you need a row for every column regardless of NULLs, fall back to UNION ALL, which preserves them.

State this trade-off in an interview: native UNPIVOT is concise but lossy on NULLs; UNION ALL is verbose but complete.

-- UNPIVOT: q3 NULL for East -> no (East, Q3) row produced
-- UNION ALL: (East, 'Q3', NULL) row IS produced

PostgreSQL: LATERAL VALUES

PostgreSQL has no UNPIVOT, but a tidy idiom is a CROSS JOIN LATERAL over a VALUES list. Each wide row is expanded against a small inline table of (label, value) pairs.

This is cleaner than a long UNION ALL and reads the source table only once.

SELECT w.region, v.quarter, v.amount
FROM wide_sales w
CROSS JOIN LATERAL (VALUES
  ('Q1', w.q1),
  ('Q2', w.q2),
  ('Q3', w.q3),
  ('Q4', w.q4)
) AS v(quarter, amount);

Reading the Table Once

A performance point worth raising: the naive UNION ALL scans the wide table once per branch (four scans for four quarters). The LATERAL VALUES form, and SQL Server UNPIVOT, read the source once.

On large tables this matters. If you must use UNION ALL, an optimizer may still scan repeatedly, so mention LATERAL or UNPIVOT as the more efficient option.

Filtering Out Empty Cells

With UNION ALL or LATERAL you keep NULL value rows. If the question wants only populated cells, add a filter. This mimics what SQL Server UNPIVOT does automatically.

Deciding whether to keep or drop NULLs is a judgment call, so clarify the requirement with the interviewer before coding.

SELECT region, quarter, amount
FROM (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
) t
WHERE amount IS NOT NULL;

Worked Example: Aggregating After Unpivot

A common follow-up: "from the wide quarterly table, give total revenue per region across all quarters." Once you unpivot to long form, the aggregation is trivial: a single SUM grouped by region.

This demonstrates the real reason to unpivot first. Summing four separate columns is brittle, but a long-form SUM(amount) GROUP BY region scales to any number of quarters.

WITH long_sales AS (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
  UNION ALL SELECT region, 'Q3', q3 FROM wide_sales
  UNION ALL SELECT region, 'Q4', q4 FROM wide_sales
)
SELECT region, SUM(amount) AS total
FROM long_sales
GROUP BY region;

When to Unpivot

Recognize the unpivot signal in a word problem:

  • The input has repeated columns that are really values (months, years, metrics).
  • You need to aggregate, join, or chart across those values.
  • You want to normalize denormalized spreadsheet data on import.

Long form is almost always the right shape for further SQL work, so unpivoting is a frequent first step.

Quick Check

Confirm you know the most common unpivot pitfall.

Recap

Unpivot turns columns into rows:

  • Portable: one SELECT per column joined with UNION ALL (never plain UNION).
  • SQL Server: native UNPIVOT, concise but drops NULL values.
  • Postgres: CROSS JOIN LATERAL (VALUES ...), single scan.
  • Align column counts and types across branches; filter NULLs if the question requires.

Frequently asked questions

Is the “Unpivoting Columns Into Rows” lesson free?

Yes — the full text of “Unpivoting Columns Into Rows” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Unpivoting Columns Into Rows”?

Reversing wide tables with UNPIVOT or UNION ALL. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Unpivoting Columns Into Rows” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Pivoting With Conditional Aggregation
  2. Vendor PIVOT and Crosstab Syntax
  3. Unpivoting Columns Into Rows
  4. Dynamic Pivots With Unknown Columns
← Back to SQL Interview Prep