By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Matches
([ID] int, [Home_Team] varchar(9), [Away_Team] varchar(9), [Date] varchar(10), [Home_Elo] int, [Away_Elo] int);
INSERT INTO Matches([ID], [Home_Team], [Away_Team], [Date], [Home_Elo], [Away_Elo])VALUES
(1, 'Turku', 'Haka', '13.09.1999', NULL, NULL),
(2, 'Pori', 'Turku', '12.09.1999', NULL, NULL),
(3, 'Joker', 'Rovaniemi', '10.09.1999', NULL, NULL),
(4, 'TeePee', 'Rovaniemi', '11.09.1999', NULL, NULL),
(5, 'Joker', 'TeePee', '20.09.1999', NULL, NULL),
(6, 'Rovaniemi', 'Joker', '12.09.1999', NULL, NULL);
6 rows affected
select m.id, m.home_team, m.away_team, m.date,
max(case t.rn when 1 then case homeaway when 1 then 1500 end end) home_elo,
max(case t.rn when 1 then case homeaway when 2 then 1500 end end) away_elo
from matches m inner join (
select u.*,
row_number() over (partition by team order by date) rn
from (
select 1 homeaway, home_team team, date from matches
union all
select 2, away_team, date from matches
) u
) t on t.date = m.date and t.team in (m.home_team, m.away_team)
group by m.id, m.home_team, m.away_team, m.date
order by m.date, m.home_team, m.away_team
| id | home_team | away_team | date | home_elo | away_elo |
|---|---|---|---|---|---|
| 3 | Joker | Rovaniemi | 10.09.1999 | 1500 | 1500 |
| 4 | TeePee | Rovaniemi | 11.09.1999 | 1500 | null |
| 2 | Pori | Turku | 12.09.1999 | 1500 | 1500 |
| 6 | Rovaniemi | Joker | 12.09.1999 | null | null |
| 1 | Turku | Haka | 13.09.1999 | null | 1500 |
| 5 | Joker | TeePee | 20.09.1999 | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.