Conditional pivot

You meet it when#

Someone wants the report as a grid: months across the top, categories down the side. Long data is right for storage and modelling; wide is right for a human reading a table.

The pattern#

select
    country,
    sum(case when channel = 'web'         then revenue_usd else 0 end) as web,
    sum(case when channel = 'ios'         then revenue_usd else 0 end) as ios,
    sum(case when channel = 'android'     then revenue_usd else 0 end) as android,
    sum(case when channel = 'marketplace' then revenue_usd else 0 end) as marketplace,
    sum(revenue_usd)                                                   as total
from retail_orders
group by country
order by total desc;

That is the whole pattern. It works in every engine, it is obvious to read, and it needs no extension.

else 0 versus else null#

They differ and the difference matters:

• else 0 — an empty cell shows 0. Right when the measure is additive and a missing combination genuinely means none.
• else null — an empty cell shows nothing. Right when you are going to avg() it, because avg skips nulls and averaging in false zeros drags the answer down.

Choose deliberately. This is the same zero-versus-missing distinction as the date spine.

Counting instead of summing#

count(case when arr_delay_min > 15 then 1 end) as late_flights,
count(*)                                        as total_flights,
round(100.0 * count(case when arr_delay_min > 15 then 1 end) / count(*), 1) as late_pct

count ignores nulls, so no else branch is needed — the row simply is not counted. This is the cleanest way to write a conditional rate.

Engines with a native PIVOT clause exist, and their syntax is shorter and less portable. The case version is what I would still write.