Oracle create league table from football results

Having the below table

DATE_MATCH	HOME_TEAM	AWAY_TEAM	HOME_GOALS	AWAY_GOALS
03-APR-21	Alaves	Eibar	2	1
02-APR-21	Alaves	Huesca	3	0
01-APR-21	Eibar	Huesca	1	1
31-MAR-21	Huesca	Eibar	2	0
30-MAR-21	Eibar	Alaves	1	1
29-MAR-21	Huesca	Alaves	1	0

How to generate the table:

WITH aux AS (
    SELECT
        sysdate date_match,
        'Alaves' home_team,
        'Eibar' away_team,
        2 home_goals,
        1 away_goals
    FROM
        dual
    UNION ALL
    SELECT
        sysdate - 1 date_match,
        'Alaves' home_team,
        'Huesca' away_team,
        3 home_goals,
        0 away_goals
    FROM
        dual
    UNION ALL
    SELECT
        sysdate - 2 date_match,
        'Eibar' home_team,
        'Huesca' away_team,
        1 home_goals,
        1 away_goals
    FROM
        dual
    UNION ALL
    SELECT
        sysdate - 3 date_match,
        'Huesca' home_team,
        'Eibar' away_team,
        2 home_goals,
        0 away_goals
    FROM
        dual
    UNION ALL
    SELECT
        sysdate - 4 date_match,
        'Eibar' home_team,
        'Alaves' away_team,
        1 home_goals,
        1 away_goals
    FROM
        dual
    UNION ALL
    SELECT
        sysdate - 5 date_match,
        'Huesca' home_team,
        'Alaves' away_team,
        1 home_goals,
        0 away_goals
    FROM
        dual
)
SELECT
    *
FROM
    aux

I’d like to get the below one (league table). It would be ideal if it was possible just using pivot and unpivot clauses:

TEAM	W	D	L	GOALS_SCORED	GOALS_RECEIVED	POINTS
Alaves	2	1	1	6	3	7
Huesca	2	1	1	4	4	7
Eibar	0	2	2	3	6	2

Answer

unpivot, process, pivot is how I would do it too. However, I do the “pivoting” the “old way” (conditional aggregation), because you don’t really want to “pivot”. You are counting the wins, draws and losses separately, but the goals scored and goals received are aggregated over all results; that is not “pivoting”. For this kind of mixed aggregation (some is conditional, some is not), the good old group by is a better tool.

Something like this:

with
  aux (date_match, home_team, away_team, home_goals, away_goals) as (
    select sysdate    , 'Alaves', 'Eibar' , 2, 1 from dual union all
    select sysdate - 1, 'Alaves', 'Huesca', 3, 0 from dual union all
    select sysdate - 2, 'Eibar' , 'Huesca', 1, 1 from dual union all
    select sysdate - 3, 'Huesca', 'Eibar' , 2, 0 from dual union all
    select sysdate - 4, 'Eibar' , 'Alaves', 1, 1 from dual union all
    select sysdate - 5, 'Huesca', 'Alaves', 1, 0 from dual
  )
, prep (ht, at, hgs, ags, hgr, agr, hres, ares) as (
    select home_team, away_team, home_goals, away_goals, away_goals, home_goals,
           case sign(home_goals - away_goals)
                when 1 then 'W' when 0 then 'D' else 'L' end,
           case sign(home_goals - away_goals)
                when 1 then 'L' when 0 then 'D' else 'W' end
    from   aux
  )
select  team, 
        count(case res when 'W' then 1 end) as w,
        count(case res when 'D' then 1 end) as d,
        count(case res when 'L' then 1 end) as l,
        sum(gs) as goals_scored, sum(gr) as goals_received,
        sum(case res when 'W' then 3 when 'D' then 1 else 0 end) as points
from    prep
unpivot ((team, gs, gr, res) for loc in ((ht, hgs, hgr, hres) as 'X',
                                         (at, ags, agr, ares) as 'X'))
group   by team
order   by points desc, goals_scored - goals_received desc, goals_scored desc
;

Output:

TEAM            W          D          L GOALS_SCORED GOALS_RECEIVED     POINTS
------ ---------- ---------- ---------- ------------ -------------- ----------
Alaves          2          1          1            6              3          7
Huesca          2          1          1            4              4          7
Eibar           0          2          2            3              6          2

Notice how I rearranged your sample data (the first subquery in the with clause) so that it fits in just a few lines of code. You aren’t paid by the line on this site, so there is no need to do in 60 lines what can be done in 8.