SQL Server 2017

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

CREATE TABLE mecze(
   ID      INTEGER  NOT NULL PRIMARY KEY 
  ,Country VARCHAR(6) NOT NULL
  ,League  VARCHAR(7) NOT NULL
  ,Season  VARCHAR(9) NOT NULL
  ,data    varchar(10)  NOT NULL
  ,Time    VARCHAR(16) NOT NULL
  ,Home    VARCHAR(21) NOT NULL
  ,Away    VARCHAR(21) NOT NULL
  ,HG      INTEGER  NOT NULL
  ,AG      INTEGER  NOT NULL
  ,Res     VARCHAR(1) NOT NULL
);
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41839,'France','Ligue 1','2012/2013','11.08.2012','21:00:00.0000000','Paris SG','Lorient',2,2,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41852,'France','Ligue 1','2012/2013','19.08.2012','21:00:00.0000000','Ajaccio','Paris SG',0,0,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41863,'France','Ligue 1','2012/2013','26.08.2012','21:00:00.0000000','Paris SG','Bordeaux',0,0,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41873,'France','Ligue 1','2012/2013','02.09.2012','21:00:00.0000000','Lille','Paris SG',1,2,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41875,'France','Ligue 1','2012/2013','14.09.2012','21:00:00.0000000','Paris SG','Toulouse',2,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41886,'France','Ligue 1','2012/2013','22.09.2012','21:00:00.0000000','Bastia','Paris SG',0,4,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41900,'France','Ligue 1','2012/2013','29.09.2012','21:00:00.0000000','Paris SG','Sochaux',2,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41914,'France','Ligue 1','2012/2013','07.10.2012','21:00:00.0000000','Marseille','Paris SG',2,2,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41919,'France','Ligue 1','2012/2013','20.10.2012','21:00:00.0000000','Paris SG','Reims',1,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41928,'France','Ligue 1','2012/2013','27.10.2012','21:00:00.0000000','Nancy','Paris SG',0,1,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41936,'France','Ligue 1','2012/2013','03.11.2012','21:00:00.0000000','Paris SG','St Etienne',1,2,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41952,'France','Ligue 1','2012/2013','11.11.2012','21:00:00.0000000','Montpellier','Paris SG',1,1,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41958,'France','Ligue 1','2012/2013','17.11.2012','21:00:00.0000000','Paris SG','Rennes',1,2,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41967,'France','Ligue 1','2012/2013','24.11.2012','21:00:00.0000000','Paris SG','Troyes',4,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41980,'France','Ligue 1','2012/2013','01.12.2012','21:00:00.0000000','Nice','Paris SG',2,1,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41988,'France','Ligue 1','2012/2013','08.12.2012','21:00:00.0000000','Paris SG','Evian Thonon Gaillard',4,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (41997,'France','Ligue 1','2012/2013','11.12.2012','21:00:00.0000000','Valenciennes','Paris SG',0,4,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42013,'France','Ligue 1','2012/2013','16.12.2012','21:00:00.0000000','Paris SG','Lyon',1,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42015,'France','Ligue 1','2012/2013','21.12.2012','21:00:00.0000000','Brest','Paris SG',0,3,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42025,'France','Ligue 1','2012/2013','11.01.2013','21:00:00.0000000','Paris SG','Ajaccio',0,0,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42043,'France','Ligue 1','2012/2013','20.01.2013','21:00:00.0000000','Bordeaux','Paris SG',0,1,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42053,'France','Ligue 1','2012/2013','27.01.2013','21:00:00.0000000','Paris SG','Lille',1,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42055,'France','Ligue 1','2012/2013','01.02.2013','21:00:00.0000000','Toulouse','Paris SG',0,4,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42065,'France','Ligue 1','2012/2013','08.02.2013','21:00:00.0000000','Paris SG','Bastia',3,1,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42084,'France','Ligue 1','2012/2013','17.02.2013','21:00:00.0000000','Sochaux','Paris SG',3,2,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42094,'France','Ligue 1','2012/2013','24.02.2013','21:00:00.0000000','Paris SG','Marseille',2,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42098,'France','Ligue 1','2012/2013','02.03.2013','21:00:00.0000000','Reims','Paris SG',1,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42109,'France','Ligue 1','2012/2013','09.03.2013','21:00:00.0000000','Paris SG','Nancy',2,1,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42123,'France','Ligue 1','2012/2013','17.03.2013','21:00:00.0000000','St Etienne','Paris SG',2,2,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42125,'France','Ligue 1','2012/2013','29.03.2013','21:00:00.0000000','Paris SG','Montpellier',1,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42139,'France','Ligue 1','2012/2013','06.04.2013','21:00:00.0000000','Rennes','Paris SG',0,2,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42151,'France','Ligue 1','2012/2013','13.04.2013','21:00:00.0000000','Troyes','Paris SG',0,1,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42159,'France','Ligue 1','2012/2013','21.04.2013','21:00:00.0000000','Paris SG','Nice',3,0,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42172,'France','Ligue 1','2012/2013','28.04.2013','21:00:00.0000000','Evian Thonon Gaillard','Paris SG',0,1,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42183,'France','Ligue 1','2012/2013','05.05.2013','21:00:00.0000000','Paris SG','Valenciennes',1,1,'D');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42194,'France','Ligue 1','2012/2013','12.05.2013','21:00:00.0000000','Lyon','Paris SG',0,1,'A');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42198,'France','Ligue 1','2012/2013','18.05.2013','21:00:00.0000000','Paris SG','Brest',3,1,'H');
INSERT INTO mecze(ID,Country,League,Season,data,Time,Home,Away,HG,AG,Res) VALUES (42210,'France','Ligue 1','2012/2013','26.05.2013','21:00:00.0000000','Lorient','Paris SG',1,3,'A');
38 rows affected

with dane as (
    select 
        country
        ,Season
        ,data
        ,home team
        ,hg
        ,ag 
    from 
        MECZE
    union all
    select 
        country
        ,Season
        ,data
        ,away team
        ,ag
        ,hg 
    from
        MECZE 
) 
,last5 as (
select 
    * 
from (
    select 
        *
        ,row_number() over (partition by country ,team order by data desc) as mecz
    from 
        dane) X
    where 
        mecz <= 5 
)
,punkty as (
select 
    team, 
    count(*) MP, 
    count(case when hg > ag then 1 end) W, 
    count(case when hg = ag then 1 end) D, 
    count(case when hg < ag then 1 end) L,
    sum(hg) GF,
    sum(ag) GA,
    sum(hg) - sum(ag) GD,
    sum(case when hg > ag then 3 else 0 end + case when hg = ag then 1 else 0 end) Pts
from 
    dane
where 
    country='france' 
    and  Season ='2012/2013'
group by 
    country
    ,team 
)
,pvtLast5 as (
SELECT 
    team 
    ,[1]
    ,[2]
    ,[3]
    ,[4]
    ,[5]
FROM  
    (SELECT 
        team
        ,data+' (' + case when hg > ag then '3' when hg = ag then '1' else '0' end+')' data
        ,mecz 
    FROM 
        last5) AS src  
    PIVOT  (  
        max(data) FOR mecz IN ( [1], [2], [3], [4],[5])  
    ) AS pvt
)

select 
    p.*
    ,[1]
    ,[2]
    ,[3]
    ,[4]
    ,[5]
from 
    punkty p
    inner join pvtlast5 l5 on p.team=l5.team
order by
    pts desc


team
MP
W
D
L
GF
GA
GD
Pts
1
2
3
4
5


Paris SG
38
25
8
5
69
23
46
83
29.09.2012 (3)
29.03.2013 (3)
28.04.2013 (3)
27.10.2012 (3)
27.01.2013 (3)

St Etienne
2
1
1
0
4
3
1
4
17.03.2013 (1)
03.11.2012 (3)
null
null
null

Reims
2
1
0
1
1
1
0
3
20.10.2012 (0)
02.03.2013 (3)
null
null
null

Rennes
2
1
0
1
2
3
-1
3
17.11.2012 (3)
06.04.2013 (0)
null
null
null

Sochaux
2
1
0
1
3
4
-1
3
29.09.2012 (0)
17.02.2013 (3)
null
null
null

Nice
2
1
0
1
2
4
-2
3
21.04.2013 (0)
01.12.2012 (3)
null
null
null

Ajaccio
2
0
2
0
0
0
0
2
19.08.2012 (1)
11.01.2013 (1)
null
null
null

Lorient
2
0
1
1
3
5
-2
1
26.05.2013 (0)
11.08.2012 (1)
null
null
null

Bordeaux
2
0
1
1
0
1
-1
1
26.08.2012 (1)
20.01.2013 (0)
null
null
null

Marseille
2
0
1
1
2
4
-2
1
24.02.2013 (0)
07.10.2012 (1)
null
null
null

Montpellier
2
0
1
1
1
2
-1
1
29.03.2013 (0)
11.11.2012 (1)
null
null
null

Valenciennes
2
0
1
1
1
5
-4
1
11.12.2012 (0)
05.05.2013 (1)
null
null
null

Nancy
2
0
0
2
1
3
-2
0
27.10.2012 (0)
09.03.2013 (0)
null
null
null

Toulouse
2
0
0
2
0
6
-6
0
14.09.2012 (0)
01.02.2013 (0)
null
null
null

Troyes
2
0
0
2
0
5
-5
0
24.11.2012 (0)
13.04.2013 (0)
null
null
null

Brest
2
0
0
2
1
6
-5
0
21.12.2012 (0)
18.05.2013 (0)
null
null
null

Evian Thonon Gaillard
2
0
0
2
0
5
-5
0
28.04.2013 (0)
08.12.2012 (0)
null
null
null

Lille
2
0
0
2
1
3
-2
0
27.01.2013 (0)
02.09.2012 (0)
null
null
null

Lyon
2
0
0
2
0
2
-2
0
16.12.2012 (0)
12.05.2013 (0)
null
null
null

Bastia
2
0
0
2
1
7
-6
0
22.09.2012 (0)
08.02.2013 (0)
null
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.

team	MP	W	D	L	GF	GA	GD	Pts	1	2	3	4	5
Paris SG	38	25	8	5	69	23	46	83	29.09.2012 (3)	29.03.2013 (3)	28.04.2013 (3)	27.10.2012 (3)	27.01.2013 (3)
St Etienne	2	1	1	0	4	3	1	4	17.03.2013 (1)	03.11.2012 (3)	null	null	null
Reims	2	1	0	1	1	1	0	3	20.10.2012 (0)	02.03.2013 (3)	null	null	null
Rennes	2	1	0	1	2	3	-1	3	17.11.2012 (3)	06.04.2013 (0)	null	null	null
Sochaux	2	1	0	1	3	4	-1	3	29.09.2012 (0)	17.02.2013 (3)	null	null	null
Nice	2	1	0	1	2	4	-2	3	21.04.2013 (0)	01.12.2012 (3)	null	null	null
Ajaccio	2	0	2	0	0	0	0	2	19.08.2012 (1)	11.01.2013 (1)	null	null	null
Lorient	2	0	1	1	3	5	-2	1	26.05.2013 (0)	11.08.2012 (1)	null	null	null
Bordeaux	2	0	1	1	0	1	-1	1	26.08.2012 (1)	20.01.2013 (0)	null	null	null
Marseille	2	0	1	1	2	4	-2	1	24.02.2013 (0)	07.10.2012 (1)	null	null	null
Montpellier	2	0	1	1	1	2	-1	1	29.03.2013 (0)	11.11.2012 (1)	null	null	null
Valenciennes	2	0	1	1	1	5	-4	1	11.12.2012 (0)	05.05.2013 (1)	null	null	null
Nancy	2	0	0	2	1	3	-2	0	27.10.2012 (0)	09.03.2013 (0)	null	null	null
Toulouse	2	0	0	2	0	6	-6	0	14.09.2012 (0)	01.02.2013 (0)	null	null	null
Troyes	2	0	0	2	0	5	-5	0	24.11.2012 (0)	13.04.2013 (0)	null	null	null
Brest	2	0	0	2	1	6	-5	0	21.12.2012 (0)	18.05.2013 (0)	null	null	null
Evian Thonon Gaillard	2	0	0	2	0	5	-5	0	28.04.2013 (0)	08.12.2012 (0)	null	null	null
Lille	2	0	0	2	1	3	-2	0	27.01.2013 (0)	02.09.2012 (0)	null	null	null
Lyon	2	0	0	2	0	2	-2	0	16.12.2012 (0)	12.05.2013 (0)	null	null	null
Bastia	2	0	0	2	1	7	-6	0	22.09.2012 (0)	08.02.2013 (0)	null	null	null