Several measures at once, and columns you don't know yet

PIVOT can aggregate more than one thing per column value: runs and wickets for each phase. The column names are glued together as <IN alias>_<measure alias>.

The test case

For each team in 2026: runs scored and wickets lost in the powerplay and at the death.

Expected result

Tampa lose the fewest powerplay wickets (4) and the most at the death (16): they start carefully and finish like they've a flight to catch.

Way 1: PIVOT with two measures

select *
from   (select t.team_code,
               case when b.over_no <= 6 then 'PP' when b.over_no >= 16 then 'DEATH' end as phase,
               b.total_runs, b.is_wicket
        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) as runs, sum(is_wicket) as wkts
        for phase in ('PP' as pp, 'DEATH' as death))
order  by team_code;

Two measures × two values = four columns: PP_RUNS, PP_WKTS, DEATH_RUNS, DEATH_WKTS. The middle overs get phase null, which isn't in the IN list, so their rows are simply ignored.

Way 2: conditional aggregation

select t.team_code,
       sum(case when b.over_no <= 6  then b.total_runs end) as pp_runs,
       sum(case when b.over_no <= 6  then b.is_wicket  end) as pp_wkts,
       sum(case when b.over_no >= 16 then b.total_runs end) as death_runs,
       sum(case when b.over_no >= 16 then b.is_wicket  end) as death_wkts
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;

Going back: UNPIVOT several columns at once

UNPIVOT can also take pairs of columns and turn each pair into one row:

unpivot ((runs, wkts) for phase in ((pp_runs, pp_wkts) as 'PP', (death_runs, death_wkts) as 'DEATH'))

When you don't know the columns in advance

The IN list must be typed into the query. If the values are only known at run time (every season in the table, every venue), there are three ways out:

1. Generate the SQL, then run it. Build the IN list with LISTAGG and run the statement with EXECUTE IMMEDIATE or open a cursor on it in PL/SQL (or let APEX run it as a "function returning SQL" report):

   select 'select * from (select t.team_code, v.city, m.match_id from cr_matches m '
       || 'join cr_venues v on v.venue_id = m.venue_id join cr_teams t on t.team_id = m.winner_id) '
       || 'pivot (count(match_id) for city in ('
       || listagg('''' || city || ''' as "' || city || '"', ', ') within group (order by city)
       || ')) order by team_code' as pivot_sql
   from   cr_venues;

2. PIVOT XML: pivot xml (count(*) for city in (any)) takes every value, but returns one XML column instead of real columns. Good for programs, awkward for people.

3. Don't pivot in SQL: return the tall result and let the report tool (APEX, Excel, the app) spread it. Often the best answer.

19c and 26ai

The same on both.

Gotchas

• Column names are <value alias>_<measure alias>. Without aliases you get names like 'PP'_SUM(TOTAL_RUNS), which need double quotes forever after.
• Dynamic SQL built from data must be quoted carefully. A venue called O'Brien Park breaks the string unless you double the quote (or use DBMS_ASSERT.ENQUOTE_LITERAL).