SQL Pivot Data

Turn category rows into side-by-side columns with conditional aggregation.

What & Why

PostgreSQL can pivot a fixed category set with either CASE WHEN inside an aggregate or the aggregate FILTER clause. This lesson uses FILTER because each output column states its variant condition directly.

Every conditional count reads the same experiment group, so the output remains one row per experiment_name while control and alternative metrics become separate columns.

See How It Works

BUSINESS QUESTION

Show control and alternative enrollment and conversion counts as separate columns per experiment.

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

SELECT
  experiment_name,
  COUNT(*) FILTER (WHERE variant = 'control') AS control_users,
  COUNT(*) FILTER (WHERE variant <> 'control') AS alternative_users,
  COUNT(*) FILTER (WHERE variant = 'control' AND converted IS TRUE) AS control_conversions,
  COUNT(*) FILTER (WHERE variant <> 'control' AND converted IS TRUE) AS alternative_conversions
FROM product.ab_tests
GROUP BY experiment_name
ORDER BY experiment_name;

RESULT — control and alternative variants pivoted

experiment_name	control_users	alternative_users	control_conversions	alternative_conversions
Onboarding Copy	1	1	1	0
Upgrade Prompt	1	1	1	0

short_copy and annual_first are counted in the alternative columns because both values differ from control.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice