UNPIVOT: turning columns into rows

The opposite turn. Our matches table has two team columns, home_team_id and away_team_id. That's natural for a fixture list, and awkward the moment you want anything per team: every team question needs "this team is the home team or the away team". UNPIVOT splits each match into two rows, one per team, so a plain GROUP BY works.

The test case

For each team, the matches played as the home side and as the away side over both seasons, and how many of each they won.

Expected result

Home advantage is real here: home sides won 23 of the 32 matches, away sides 8 (plus the tie). Tampa never won away from home. Maybe it's the jet lag; maybe it's the Florida humidity they miss.

Way 1: UNPIVOT

select t.team_code, u.side,
       count(*)                                          as matches,
       sum(case when u.winner_id = u.team_id then 1 else 0 end) as won
from   (select match_id, winner_id, home_team_id, away_team_id from cr_matches)
unpivot (team_id for side in (home_team_id as 'HOME', away_team_id as 'AWAY')) u
join   cr_teams t on t.team_id = u.team_id
group  by t.team_code, u.side
order  by t.team_code, u.side desc;

• team_id is the new column that holds the values; side is the new column that says which old column each value came from ('HOME' or 'AWAY').
• Columns not listed (match_id, winner_id) are copied onto both rows.
• u.side desc puts HOME before AWAY, which is how fixtures are usually read.

Way 2: UNION ALL

with sides as (
  select match_id, winner_id, home_team_id as team_id, 'HOME' as side from cr_matches
  union all
  select match_id, winner_id, away_team_id,           'AWAY'         from cr_matches
)
select t.team_code, s.side, count(*) as matches,
       sum(case when s.winner_id = s.team_id then 1 else 0 end) as won
from   sides s
join   cr_teams t on t.team_id = s.team_id
group  by t.team_code, s.side
order  by t.team_code, s.side desc;

The portable way. It reads the table twice; for 32 matches, nobody will notice.

Way 3: join each match to a two-row list

select t.team_code, x.side, count(*) as matches,
       sum(case when m.winner_id = t.team_id then 1 else 0 end) as won
from   cr_matches m
cross  join (select 'HOME' as side from dual union all select 'AWAY' from dual) x
join   cr_teams t on t.team_id = case x.side when 'HOME' then m.home_team_id else m.away_team_id end
group  by t.team_code, x.side
order  by t.team_code, x.side desc;

Each match meets both rows of the little list, and the CASE picks the right team column. A useful pattern when the columns to unpivot need different handling.

19c and 26ai

The same on both. On 26ai the two-row list can be written (values ('HOME'), ('AWAY')) x (side) (page 12.3).

Gotchas

• UNPIVOT drops nulls by default. If a column is null, that row disappears. Use UNPIVOT INCLUDE NULLS (...) to keep it.
• All the unpivoted columns must have the same type (here both are numbers). Mixing a number and a date gives ORA-01790; convert them first.
• Don't store data wide just because reports are wide. Tall tables are easier to query; pivot on the way out.