PIVOT: turning rows into columns

Data usually arrives tall: one row per team per phase per season. People like to read it wide: one row per team, one column per phase. PIVOT does the turn: it groups, aggregates, and spreads the values of one column across new columns.

A T20 innings has three phases, and every commentator talks about them:

The test case

Runs scored by each team in 2026, in each phase, one row per team.

Expected result

The death-over column is small because many innings end early: all out, or the chase done before the 20th over. Leeds lead the death overs by a distance: Harry Whitcombe likes the last five.

How PIVOT runs

Way 1: PIVOT

select *
from   (select t.team_code,
               case when b.over_no <= 6  then 'PP'
                    when b.over_no <= 15 then 'MID'
                    else 'DEATH' end as phase,
               b.total_runs
        from   cr_balls_v b
        join   cr_teams t on t.team_id = b.batting_team_id
        where  b.season = 2026)
pivot  (sum(total_runs) for phase in ('PP' as pp, 'MID' as mid, 'DEATH' as death))
order  by team_code;

• FOR phase IN ('PP' AS pp, ...) lists the values that become columns, and names them. Without AS, the column would be called 'PP', quotes and all.
• The subquery matters: PIVOT groups by every column it isn't told about. Feed it only the columns you need (Gotchas below).

Way 2: conditional aggregation

select t.team_code,
       sum(case when b.over_no <= 6              then b.total_runs end) as pp,
       sum(case when b.over_no between 7 and 15  then b.total_runs end) as mid,
       sum(case when b.over_no >= 16             then b.total_runs end) as death
from   cr_balls_v b
join   cr_teams t on t.team_id = b.batting_team_id
where  b.season = 2026
group  by t.team_code
order  by t.team_code;

This is what PIVOT does underneath, and it works in every database. It's also more flexible: each column can have its own condition and its own aggregate.

Way 3: DECODE, the 1990s version

select team_code,
       sum(decode(phase, 'PP', total_runs))    as pp,
       sum(decode(phase, 'MID', total_runs))   as mid,
       sum(decode(phase, 'DEATH', total_runs)) as death
from   (select t.team_code,
               case when b.over_no <= 6 then 'PP' when b.over_no <= 15 then 'MID' else 'DEATH' end as phase,
               b.total_runs
        from   cr_balls_v b
        join   cr_teams t on t.team_id = b.batting_team_id
        where  b.season = 2026)
group  by team_code
order  by team_code;

You'll meet this in old code. DECODE(phase, 'PP', total_runs) means "total_runs if phase is 'PP', otherwise null". It still works; CASE is clearer.

19c and 26ai

The same on both. Oracle has had PIVOT since 11g.

Gotchas

• The hidden GROUP BY. Pivot straight from cr_balls_v and every other column (match, over, bowler...) joins the grouping: you get thousands of rows, each with one non-null cell. Always pivot a subquery that holds just the grouping column, the spreading column and the value.
• The IN list must be constants. IN (select ...) isn't allowed in a normal pivot; for a list you only know at run time, see page 7.2.
• Missing combinations give null, not 0. Wrap with NVL(pp, 0) in the outer select if you want 0.