SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Match(
id int, 
matchdate date, 
home_player1 int, 
home_player2 int, 
home_player3 int, 
away_player1 int, 
away_player2 int, 
away_player3 int)

create table Player (
id int,
name varchar(100)
)
insert into Player(id, name)
values 
(1, 'Bernd Leno (1)'), 
(2, 'Aaron Ramsdate (32)'), 
(3, 'Kieran Tierney (3)'), 
(4, 'Nick Pope (1)'), 
(5, 'Will Norris (25)'), 
(6, 'Wayne Hennessey (13)')
insert into Match (id, matchdate, home_player1, home_player2, home_player3, away_player1, away_player2, away_player3)
values
(1, '2021-09-29', 1, 2, 3, 4, 5, 6),
(2, '2021-09-30', 4, 5, 6, 1, 2, 3)
create table MatchPlayer (match_id int, player_id int)
select match_id, player_id from 
(select Match.id as match_id, Match.home_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player3 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player3 as player_id from Match

) as unpivottable



match_id
player_id


1
1

2
4

1
2

2
5

1
3

2
6

1
4

2
1

1
5

2
2

1
6

2
3


insert into matchplayer
select match_id, player_id from 
(select Match.id as match_id, Match.home_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.home_player3 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player1 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player2 as player_id from Match
UNION ALL
select Match.id as match_id, Match.away_player3 as player_id from Match

) as unpivottable
where unpivottable.match_id 
SELECT m.id, m.matchdate, p.name  
FROM Match as m 
    JOIN MatchPlayer as mp
        ON mp.match_id = m.id
    JOIN Player as p
        ON mp.player_id = p.id


id
matchdate
name


1
2021-09-29
Bernd Leno (1)

1
2021-09-29
Aaron Ramsdate (32)

1
2021-09-29
Kieran Tierney (3)

1
2021-09-29
Nick Pope (1)

1
2021-09-29
Will Norris (25)

1
2021-09-29
Wayne Hennessey (13)

2
2021-09-30
Bernd Leno (1)

2
2021-09-30
Aaron Ramsdate (32)

2
2021-09-30
Kieran Tierney (3)

2
2021-09-30
Nick Pope (1)

2
2021-09-30
Will Norris (25)

2
2021-09-30
Wayne Hennessey (13)

id	matchdate	name
1	2021-09-29	Bernd Leno (1)
1	2021-09-29	Aaron Ramsdate (32)
1	2021-09-29	Kieran Tierney (3)
1	2021-09-29	Nick Pope (1)
1	2021-09-29	Will Norris (25)
1	2021-09-29	Wayne Hennessey (13)
2	2021-09-30	Bernd Leno (1)
2	2021-09-30	Aaron Ramsdate (32)
2	2021-09-30	Kieran Tierney (3)
2	2021-09-30	Nick Pope (1)
2	2021-09-30	Will Norris (25)
2	2021-09-30	Wayne Hennessey (13)