Turning rows into columns

Pivot with SUM and CASE to put categories side by side, and unpivot with UNION ALL to stack them back.

GROUP BY puts each category on its own row. Reports and spreadsheets often want categories as columns: one column per stage, per year or per region, so the eye can compare across. Turning rows into columns is called a pivot.

The long shape and the wide shape

The same numbers, two ways. Open deals per lead source and stage, long:

SELECT COALESCE(lead_source, 'Unknown') AS source, stage, COUNT(*) AS deals
FROM opportunities
WHERE closed_at IS NULL
GROUP BY source, stage;

That’s one row per source-and-stage pair: easy for a database or a chart tool, hard to read. The wide version has one row per source and one column per stage:

Pivoting with SUM and CASE

This is lesson 17’s conditional aggregation, with one CASE per output column:

SELECT COALESCE(lead_source, 'Unknown') AS source,
       SUM(CASE WHEN stage = 'Prospecting'   THEN 1 ELSE 0 END) AS prospecting,
       SUM(CASE WHEN stage = 'Qualification' THEN 1 ELSE 0 END) AS qualification,
       SUM(CASE WHEN stage = 'Proposal'      THEN 1 ELSE 0 END) AS proposal,
       SUM(CASE WHEN stage = 'Negotiation'   THEN 1 ELSE 0 END) AS negotiation
FROM opportunities
WHERE closed_at IS NULL
GROUP BY source
ORDER BY source;

Each row goes into one group. Each CASE checks whether that row belongs in its column: 1 if so, 0 if not. SUM adds them up. Swap THEN 1 for THEN amount to pivot dollars instead of counts.

The catch: you have to write a CASE for every column. SQL can’t create columns from whatever values happen to be in the data. If a new stage appears next month, it won’t get a column until someone adds one. That’s why pivots usually happen at the last step, often in the reporting tool itself.

Pivoting by year

Dates pivot the same way, with a date range in each CASE (lesson 21’s half-open range):

SELECT u.name,
       SUM(CASE WHEN o.closed_at >= '2025-01-01' AND o.closed_at < '2026-01-01'
                THEN o.amount ELSE 0 END) AS won_2025,
       SUM(CASE WHEN o.closed_at >= '2026-01-01' AND o.closed_at < '2027-01-01'
                THEN o.amount ELSE 0 END) AS won_2026
FROM users AS u
LEFT JOIN opportunities AS o
       ON o.owner_id = u.user_id
      AND o.is_won = 1
GROUP BY u.name
ORDER BY u.name;

Two details carry over from earlier lessons:

• The “won” test is in ON, not WHERE. In WHERE, it would remove Erin, who has no deals at all (lesson 15’s trap).
• Erin’s row shows 0, not NULL. She has one row from the LEFT JOIN with NULL deal columns, so each CASE falls to ELSE 0.

Unpivoting: columns back into rows

Sometimes data arrives wide, like a spreadsheet with a column per quarter, and you need it long for a chart or a join. Stack one query per column with UNION ALL (lesson 25):

WITH wide AS (
    SELECT 'Ana Ruiz' AS name, 85000 AS won_2025, 150000 AS won_2026
)
SELECT name, '2025' AS year, won_2025 AS won FROM wide
UNION ALL
SELECT name, '2026',         won_2026        FROM wide;

Each SELECT takes one column and labels where it came from.

Common mistakes

Dialect notes

SUM(CASE ...) works on every database, which is why it’s the version to learn first.

Snowflake notes

Snowflake has PIVOT and UNPIVOT:

SELECT *
FROM (SELECT lead_source, stage FROM opportunities WHERE closed_at IS NULL)
PIVOT (COUNT(stage) FOR stage IN ('Prospecting', 'Qualification', 'Proposal', 'Negotiation'));

Snowflake’s PIVOT can also take IN (ANY ORDER BY stage), which builds a column for every value in the data automatically, so it doesn’t need updating when new values appear. COUNT_IF(stage = 'Proposal') is also a shorter way to write one pivoted count.

More in the field guide: PIVOT and Conditional aggregation.

Try it

Show each rep’s won revenue with 2025 and 2026 side by side, one column per year. Every rep appears, including anyone with no wins, who shows 0. Sort by rep name. Your result should match the table above.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson