By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table StatsTable(player_Team varchar(50),match_home_team varchar(50),match_away_team varchar(50), Shots int,Shots_on_Target int,Goals int , Time int, Day varchar(50));
insert into StatsTable values('Brazil','Brazil','England',6, 3, 1, 1200, 'Saturday');
insert into StatsTable values('England','England','Brazil',3 ,1 ,0 ,1200 , 'Saturday');
2 rows affected
with cte as
(
Select
max(t.[player_Team]) team,
(case when max(t.[Player_Team]) = max(t.[match_home_team]) then max(t.[match_away_team])
else max(t.[match_home_team]) end) opposition,
sum(t.[Shots])shots,
sum(t.[Shots_on_Target])Shots_on_Target,
sum(t.[Goals])goals,
max(t.[Time])time,
max(t.[Day])day
From [StatsTable] t
Group By t.[player_Team]
)
Select
t.team,
t.opposition opposition,
t.Shots,
t.Shots_on_Target,
t.Goals,
t.[Time],
t.[Day],
o.[Shots] oppo_shots,
o.[Shots_on_Target] Oppo_Shots_on_Target,
o.[Goals] opp_Goals
From cte t inner join cte o on t.opposition=o.team
| team | opposition | Shots | Shots_on_Target | Goals | Time | Day | oppo_shots | Oppo_Shots_on_Target | opp_Goals |
|---|---|---|---|---|---|---|---|---|---|
| Brazil | England | 6 | 3 | 1 | 1200 | Saturday | 3 | 1 | 0 |
| England | Brazil | 3 | 1 | 0 | 1200 | Saturday | 6 | 3 | 1 |