Pivot in SQL: Rows to Columns with Conditional Aggregation, FILTER, and PIVOT

A pivot changes a tall result into a wide one. In SQL, the dependable pattern is conditional aggregation: choose the output row grain with GROUP BY, then write one aggregate for each fixed output column.

The column set must be known when the query is written. If the data decides how many columns exist, return a stable long or JSON shape and let the consumer expand it.

Pivot starts with the output grain and fixed columns

This report needs one row per department and one column for each of three known months. CASE routes revenue into the correct month; SUM collapses all source rows for the department. ELSE 0 states that a missing department-month cell should display zero.

Input: department_revenue

department	month_name	revenue
A	Jan	8000
B	Jan	9000
C	Feb	10000
A	Feb	7000
A	Mar	6000

SELECT
  department,
  SUM(CASE WHEN month_name = 'Jan' THEN revenue ELSE 0 END) AS jan_revenue,
  SUM(CASE WHEN month_name = 'Feb' THEN revenue ELSE 0 END) AS feb_revenue,
  SUM(CASE WHEN month_name = 'Mar' THEN revenue ELSE 0 END) AS mar_revenue
FROM department_revenue
GROUP BY department
ORDER BY department;

Output

department	jan_revenue	feb_revenue	mar_revenue
A	8000	7000	6000
B	9000	0	0
C	0	10000	0

Use ELSE 0 only when zero is the intended missing-cell policy. If a missing cell means “not observed,” omit the ELSE and let the aggregate return NULL. The CASE WHEN guide covers the expression rules behind the routing step.

Counts and zero-activity rows need a population table

To show employers with no submissions, start from employers and left join the activity table. Count a non-null submission key through each condition so the null-padded row contributes zero.

Input: employers

employer_id	employer_name
1	Acme
2	Borealis
3	Cygnus

Input: submissions

submission_id	employer_id	is_buggy
10	1	true
11	1	false
12	1	false
13	2	true

SELECT
  e.employer_id,
  e.employer_name,
  COUNT(s.submission_id) FILTER (WHERE s.is_buggy) AS buggy_count,
  COUNT(s.submission_id) FILTER (WHERE NOT s.is_buggy) AS clean_count
FROM employers AS e
LEFT JOIN submissions AS s
  ON s.employer_id = e.employer_id
GROUP BY e.employer_id, e.employer_name
ORDER BY e.employer_id;

Output

employer_id	employer_name	buggy_count	clean_count
1	Acme	1	2
2	Borealis	1	0
3	Cygnus	0	0

The CASE version is COUNT(CASE WHEN s.is_buggy THEN s.submission_id END). Do not add ELSE 0 inside COUNT; zero is non-null and would make every joined row count. See SQL COUNT for the underlying NULL behavior.

FILTER makes PostgreSQL metric pivots easier to scan

Event types often arrive as rows even though a report needs separate metric columns. First pivot clicks and impressions by format, then compute the ratio. A format with clicks but no impressions gets a NULL rate because the denominator is zero.

Input: ad_events

event_id	ad_format	event_type	event_count
1	video	impression	4
2	video	click	1
3	static	impression	6
4	carousel	click	2

WITH pivoted AS (
  SELECT
    ad_format,
    COALESCE(SUM(event_count) FILTER (WHERE event_type = 'click'), 0) AS clicks,
    COALESCE(SUM(event_count) FILTER (WHERE event_type = 'impression'), 0) AS impressions
  FROM ad_events
  GROUP BY ad_format
)
SELECT
  ad_format,
  clicks,
  impressions,
  ROUND(clicks::numeric / NULLIF(impressions, 0), 4) AS ctr
FROM pivoted
ORDER BY ad_format;

Output

ad_format	clicks	impressions	ctr
carousel	2	0	NULL
static	0	6	0.0000
video	1	4	0.2500

FILTER is supported by PostgreSQL and keeps the aggregate visible. Other engines may prefer conditional CASE expressions, so confirm the target dialect instead of assuming one spelling is universal.

A pivot can become a group predicate

Sometimes the wide columns are useful only for testing a group. Here the question is which customers bought both Kindle and Alexa. One purchase row cannot equal both names, so the conditions must be evaluated across each customer's group.

Input: purchases

purchase_id	customer_id	item_name
1	1	Kindle
2	1	Alexa
3	1	Fire TV
4	2	Kindle
5	3	Alexa
6	4	Fire TV

SELECT customer_id
FROM purchases
GROUP BY customer_id
HAVING BOOL_OR(item_name = 'Kindle')
   AND BOOL_OR(item_name = 'Alexa')
ORDER BY customer_id;

Output

customer_id
1

The CASE form uses MAX(CASE WHEN item_name = 'Kindle' THEN 1 ELSE 0 END) = 1. HAVING is the right stage because it filters groups after the conditional aggregate exists. The GROUP BY guide develops that distinction.

Dynamic columns and the reverse operation

A normal SQL result has a fixed set of columns. When month keys come from the data, a JSON object can keep the result schema stable while allowing each category to carry different keys.

Input: transactions

transaction_id	category	amount	transaction_ts
1	grocery	20.00	2026-01-05 10:00
2	grocery	15.50	2026-02-11 09:30
3	grocery	4.50	2026-01-20 18:00
4	pharmacy	30.00	2026-02-02 12:00

WITH monthly AS (
  SELECT
    category,
    TO_CHAR(transaction_ts, 'YYYY-MM') AS month_key,
    SUM(amount) AS total_amount
  FROM transactions
  GROUP BY category, TO_CHAR(transaction_ts, 'YYYY-MM')
)
SELECT
  category,
  JSONB_OBJECT_AGG(month_key, total_amount ORDER BY month_key)::text AS by_month
FROM monthly
GROUP BY category
ORDER BY category;

Output

category	by_month
grocery	{"2026-01": 24.50, "2026-02": 15.50}
pharmacy	{"2026-02": 30.00}

Another option is to return the long category, month_key, total_amount rows and pivot in the reporting layer. Generating SQL text is possible, but identifiers need allow-listing and the output schema still changes from run to run.

Unpivoting goes in the opposite direction. PostgreSQL can pair fixed program labels with their corresponding columns by unnesting two arrays. This keeps a NULL spend row so the consumer can distinguish “program exists with unknown spend” from “program row removed.”

Input: advertiser_spend

advertiser_id	advertiser_name	spend_prog_a	spend_prog_b	spend_prog_c
1	AlphaCo	1000.00	500.00	NULL
2	BetaInc	200.00	1200.00	300.00
3	GammaLLC	0.00	0.00	0.00

SELECT
  s.advertiser_id,
  s.advertiser_name,
  u.program,
  u.spend
FROM advertiser_spend AS s
CROSS JOIN LATERAL UNNEST(
  ARRAY['A', 'B', 'C'],
  ARRAY[s.spend_prog_a, s.spend_prog_b, s.spend_prog_c]
) AS u(program, spend)
ORDER BY s.advertiser_id, u.program;

Output

advertiser_id	advertiser_name	program	spend
1	AlphaCo	A	1000.00
1	AlphaCo	B	500.00
1	AlphaCo	C	NULL
2	BetaInc	A	200.00
2	BetaInc	B	1200.00
2	BetaInc	C	300.00
3	GammaLLC	A	0.00
3	GammaLLC	B	0.00
3	GammaLLC	C	0.00

Vendor PIVOT and UNPIVOT operators can shorten syntax when an engine is fixed, but they do not remove the need to define columns, aggregate duplicate cells, and choose a NULL policy. For broader analyst patterns around these reshapes, see SQL for data analysis.

FAQ

Which aggregate should I use in a pivot?

Match the measure. Use SUM for additive amounts, COUNT for occurrences, MAX or BOOL_OR for any-match flags, and an explicitly defined denominator for rates. Do not choose the aggregate only because it makes the sample output look right.

Why can a pivot not create arbitrary columns from the data?

A normal SQL statement exposes a fixed result schema when it is parsed. Return long rows or a map-like value when keys are data-dependent, or generate a carefully allow-listed statement outside the query.

Should missing pivot cells be zero or NULL?

That is a metric decision. Zero says the observed group had none of the measure. NULL says the value is missing, unknown, or not applicable. State the policy before adding ELSE 0 or COALESCE.

What is the difference between pivoting and conditional aggregation?

Conditional aggregation is the underlying row-to-column technique. Some engines also expose a PIVOT operator as dialect-specific syntax around the same fixed-column idea.

How do I debug a pivot?

Run the grouped source first at the intended row-and-column-key grain. Confirm one value exists per cell, then add one conditional column and compare its total with the long-form result before expanding the full column list.