MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE matches(
home_team_id int,
away_team_id int,
htft varchar(10) );

insert into matches  values
(18,17,'X/2'),
(20,19,'2/2'),
(1,8,'X/2'),
(4,12,'1/X'),
(14,2,'2/2'),
(3,16,'1/1'),
(13,15,'2/X'),
(7,10,'1/1'),
(5,6,'1/1'),
(9,13,'X/1'),
(19,18,'1/1'),
(8,4,'X/1'),
(12,20,'X/2'),
(17,14,'2/1'),
(2,3,'2/2'),
(10,5,'X/X'),
(15,11,'1/1'),
(6,7,'X/X'),
(16,1,'1/X'),
(3,8,'X/2'),
(11,19,'2/2'),
(15,9,'2/X'),
(18,12,'1/X'),
(5,7,'1/1'),
(13,2,'1/1'),
(14,16,'2/2'),
(20,10,'1/1'),
(1,17,'X/X'),
(4,6,'1/1'),
(2,11,'1/1'),
(10,4,'X/1'),
(12,14,'2/1'),
(19,9,'2/X'),
(8,18,'X/X'),
(6,13,'X/2'),
(5,20,'1/1'),
(16,15,'1/1'),
(7,1,'1/1'),
(17,3,'1/1');

CREATE TABLE teams (
team_id int,
team_name varchar(50) );


insert into teams  values
(1,'Adana Demirspor'),
(2,'Alanyaspor'),
(3,'Altay'),
(4,'Antalyaspor'),
(5,'Beşiktaş'),
(6,'Çaykur Rizespor'),
(7,'Fatih Karagümrük'),
(8,'Fenerbahçe'),
(9,'Galatasaray'),
(10,'Gaziantep'),
(11,'Giresunspor'),
(12,'Göztepe'),
(13,'Hatayspor'),
(14,'İstanbul Başakşehir'),
(15,'Kasımpaşa'),
(16,'Kayserispor'),
(17,'Konyaspor'),
(18,'Sivasspor'),
(19,'Trabzonspor'),
(20,'Yeni Malatyaspor');
SELECT m.home_team_id,t.team_name as Home,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1/1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'X/1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'X/X',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2/2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'X/2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1/X',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2/X',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2/1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1/2'
    FROM matches m 
    INNER JOIN teams t on m.home_team_id = t.team_id
GROUP BY m.home_team_id,Home
ORDER BY 1;


home_team_id
Home
1/1
X/1
X/X
2/2
X/2
1/X
2/X
2/1
1/2


1
Adana Demirspor
0
0
1
0
1
0
0
0
0

2
Alanyaspor
1
0
0
1
0
0
0
0
0

3
Altay
1
0
0
0
1
0
0
0
0

4
Antalyaspor
1
0
0
0
0
1
0
0
0

5
Beşiktaş
3
0
0
0
0
0
0
0
0

6
Çaykur Rizespor
0
0
1
0
1
0
0
0
0

7
Fatih Karagümrük
2
0
0
0
0
0
0
0
0

8
Fenerbahçe
0
1
1
0
0
0
0
0
0

9
Galatasaray
0
1
0
0
0
0
0
0
0

10
Gaziantep
0
1
1
0
0
0
0
0
0

11
Giresunspor
0
0
0
1
0
0
0
0
0

12
Göztepe
0
0
0
0
1
0
0
1
0

13
Hatayspor
1
0
0
0
0
0
1
0
0

14
İstanbul Başakşehir
0
0
0
2
0
0
0
0
0

15
Kasımpaşa
1
0
0
0
0
0
1
0
0

16
Kayserispor
1
0
0
0
0
1
0
0
0

17
Konyaspor
1
0
0
0
0
0
0
1
0

18
Sivasspor
0
0
0
0
1
1
0
0
0

19
Trabzonspor
1
0
0
0
0
0
1
0
0

20
Yeni Malatyaspor
1
0
0
1
0
0
0
0
0


SELECT m.away_team_id,t.team_name as Away,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1/1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'X/1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'X/X',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2/2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'X/2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1/X',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2/X',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2/1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1/2'
    FROM matches m
    INNER JOIN teams t on m.away_team_id = t.team_id
GROUP BY m.away_team_id,Away
ORDER BY 1;


away_team_id
Away
1/1
X/1
X/X
2/2
X/2
1/X
2/X
2/1
1/2


1
Adana Demirspor
1
0
0
0
0
1
0
0
0

2
Alanyaspor
1
0
0
1
0
0
0
0
0

3
Altay
1
0
0
1
0
0
0
0
0

4
Antalyaspor
0
2
0
0
0
0
0
0
0

5
Beşiktaş
0
0
1
0
0
0
0
0
0

6
Çaykur Rizespor
2
0
0
0
0
0
0
0
0

7
Fatih Karagümrük
1
0
1
0
0
0
0
0
0

8
Fenerbahçe
0
0
0
0
2
0
0
0
0

9
Galatasaray
0
0
0
0
0
0
2
0
0

10
Gaziantep
2
0
0
0
0
0
0
0
0

11
Giresunspor
2
0
0
0
0
0
0
0
0

12
Göztepe
0
0
0
0
0
2
0
0
0

13
Hatayspor
0
1
0
0
1
0
0
0
0

14
İstanbul Başakşehir
0
0
0
0
0
0
0
2
0

15
Kasımpaşa
1
0
0
0
0
0
1
0
0

16
Kayserispor
1
0
0
1
0
0
0
0
0

17
Konyaspor
0
0
1
0
1
0
0
0
0

18
Sivasspor
1
0
1
0
0
0
0
0
0

19
Trabzonspor
0
0
0
2
0
0
0
0
0

20
Yeni Malatyaspor
1
0
0
0
1
0
0
0
0


WITH
Home AS (
SELECT m.home_team_id AS team_id,t.team_name,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS 'h 1/1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'h X/1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'h X/X',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS 'h 2/2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'h X/2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS 'h 1/X',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS 'h 2/X',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS 'h 2/1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS 'h 1/2'
    FROM matches m 
    INNER JOIN teams t on m.home_team_id = t.team_id
GROUP BY 1,2
),
Away AS (
SELECT m.away_team_id AS team_id,t.team_name,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS 'a 1/1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'a X/1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'a X/X',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS 'a 2/2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'a X/2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS 'a 1/X',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS 'a 2/X',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS 'a 2/1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS 'a 1/2'
    FROM matches m
    INNER JOIN teams t on m.away_team_id = t.team_id
GROUP BY 1,2
)
SELECT *
FROM Home
JOIN Away USING (team_id, team_name)
ORDER BY 1


team_id
team_name
h 1/1
h X/1
h X/X
h 2/2
h X/2
h 1/X
h 2/X
h 2/1
h 1/2
a 1/1
a X/1
a X/X
a 2/2
a X/2
a 1/X
a 2/X
a 2/1
a 1/2


1
Adana Demirspor
0
0
1
0
1
0
0
0
0
1
0
0
0
0
1
0
0
0

2
Alanyaspor
1
0
0
1
0
0
0
0
0
1
0
0
1
0
0
0
0
0

3
Altay
1
0
0
0
1
0
0
0
0
1
0
0
1
0
0
0
0
0

4
Antalyaspor
1
0
0
0
0
1
0
0
0
0
2
0
0
0
0
0
0
0

5
Beşiktaş
3
0
0
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0

6
Çaykur Rizespor
0
0
1
0
1
0
0
0
0
2
0
0
0
0
0
0
0
0

7
Fatih Karagümrük
2
0
0
0
0
0
0
0
0
1
0
1
0
0
0
0
0
0

8
Fenerbahçe
0
1
1
0
0
0
0
0
0
0
0
0
0
2
0
0
0
0

9
Galatasaray
0
1
0
0
0
0
0
0
0
0
0
0
0
0
0
2
0
0

10
Gaziantep
0
1
1
0
0
0
0
0
0
2
0
0
0
0
0
0
0
0

11
Giresunspor
0
0
0
1
0
0
0
0
0
2
0
0
0
0
0
0
0
0

12
Göztepe
0
0
0
0
1
0
0
1
0
0
0
0
0
0
2
0
0
0

13
Hatayspor
1
0
0
0
0
0
1
0
0
0
1
0
0
1
0
0
0
0

14
İstanbul Başakşehir
0
0
0
2
0
0
0
0
0
0
0
0
0
0
0
0
2
0

15
Kasımpaşa
1
0
0
0
0
0
1
0
0
1
0
0
0
0
0
1
0
0

16
Kayserispor
1
0
0
0
0
1
0
0
0
1
0
0
1
0
0
0
0
0

17
Konyaspor
1
0
0
0
0
0
0
1
0
0
0
1
0
1
0
0
0
0

18
Sivasspor
0
0
0
0
1
1
0
0
0
1
0
1
0
0
0
0
0
0

19
Trabzonspor
1
0
0
0
0
0
1
0
0
0
0
0
2
0
0
0
0
0

20
Yeni Malatyaspor
1
0
0
1
0
0
0
0
0
1
0
0
0
1
0
0
0
0
home_team_id	Home	1/1	X/1	X/X	2/2	X/2	1/X	2/X	2/1
1	Adana Demirspor	0	0	1	0	1	0	0	0
2	Alanyaspor	1	0	0	1	0	0	0	0
3	Altay	1	0	0	0	1	0	0	0
4	Antalyaspor	1	0	0	0	0	1	0	0
5	Beşiktaş	3	0	0	0	0	0	0	0
6	Çaykur Rizespor	0	0	1	0	1	0	0	0
7	Fatih Karagümrük	2	0	0	0	0	0	0	0
8	Fenerbahçe	0	1	1	0	0	0	0	0
9	Galatasaray	0	1	0	0	0	0	0	0
10	Gaziantep	0	1	1	0	0	0	0	0
11	Giresunspor	0	0	0	1	0	0	0	0
12	Göztepe	0	0	0	0	1	0	0	1
13	Hatayspor	1	0	0	0	0	0	1	0
14	İstanbul Başakşehir	0	0	0	2	0	0	0	0
15	Kasımpaşa	1	0	0	0	0	0	1	0
16	Kayserispor	1	0	0	0	0	1	0	0
17	Konyaspor	1	0	0	0	0	0	0	1
18	Sivasspor	0	0	0	0	1	1	0	0
19	Trabzonspor	1	0	0	0	0	0	1	0
20	Yeni Malatyaspor	1	0	0	1	0	0	0	0