Using the Union function in SQL to reverse columns

I have the following SQL code to produce the following table of data:

    Select
    max([Player_Team]) as 'Team', 
    case when max([Player_Team]) = max([match_home_team]) then max([match_away_team])
 else max([match_home_team]) end as 'Opposition',  
    sum([Shots]),    
    sum([Shots on Target]),    
    sum([Goals]),    
    max([Time]),     
    max([Day])
    From [StatsTable]
    Group By [Team]
    
    Team       Opposition    Shots   Shots on Target   Goals    Time     Day
    Brazil      England         6           3            1       1200    Saturday
    England      Brazil         3            1           0       1200    Saturday

I am hoping to transform data to create a table that looks like:

    Team       Opposition    Shots    Shots on Target   Goals    Time     Day       O.Shots   O.SOT    O.Opp Goals
    Brazil      England         6           3            1       1200    Saturday      3       1          0
    England      Brazil         3            1           0       1200    Saturday      6       3          1

What would be the best way to achieve this? Something like creating two tables and then a Union join?

Answer

You can use self join to achieve what are you looking for.

Schema:

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');

Query:

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

Output:

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

db<>fiddle here