SQL Unpivot Data

The reverse of pivoting — turn columns back into rows, with UNION ALL.

What & Why

Unpivoting takes wide data (separate columns per category) and turns it back into long data (one row per category). The standard Postgres approach is a UNION ALL of one SELECT per original column.

See How It Works

BUSINESS QUESTION

Product wants control and alternative participant counts returned as one row per experiment and variant group.

id	experiment_name	variant	user_id	enrolled_at	converted	converted_at
1	Onboarding Copy	control	101	2024-01-18 10:00:00+00	true	2024-01-20 09:30:00+00
2	Onboarding Copy	short_copy	102	2024-01-18 10:05:00+00	false	NULL
3	Upgrade Prompt	control	103	2024-03-15 08:00:00+00	true	2024-03-18 12:45:00+00
4	Upgrade Prompt	annual_first	104	2024-03-15 08:04:00+00	NULL	NULL

EXAMPLE QUERY

WITH pivoted AS (
  SELECT
    experiment_name,
    COUNT(*) FILTER (WHERE variant = 'control') AS control_users,
    COUNT(*) FILTER (WHERE variant <> 'control') AS alternative_users
  FROM product.ab_tests
  GROUP BY experiment_name
)
SELECT experiment_name, 'control' AS variant_group, control_users AS users FROM pivoted
UNION ALL
SELECT experiment_name, 'alternative', alternative_users FROM pivoted
ORDER BY experiment_name, variant_group;

RESULT — pivoted variant groups returned as rows

experiment_name	variant_group	users
Onboarding Copy	alternative	1
Onboarding Copy	control	1
Upgrade Prompt	alternative	1
Upgrade Prompt	control	1

UNION ALL emits both groups for each experiment, and the final ORDER BY places alternative before control.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice