Recursive query to get totals by distinct item from a row of a data

I have SQL data that looks like the following:

ResultId    HomeTeam    HomeScore   AwayTeam    AwayScore
1045        USA         2           France      3
1046        USA         1           Botswana    1
1047        Botswana    2           France      2

How can I recurse through this data to get total wins, draws, losses by team for both home and away? My output would look something like:

Team        Wins        Draws      Losses
USA         0           1          1
France      1           1          0
Botswana    0           2          0

I was able to get this done in a very linear way by simply get a list of distinct teams and for each team looping through the data and tallying the win, draw, loss total and aggregating that into variables. However, I imagine there may be a different way to accomplish this.

Answer

You don’t need recursion here, you can just unpivot the Home and Away sides, then group by the Team and do conditional aggregation

SELECT
  v.Team,
  Wins   = COUNT(CASE WHEN ScoreDiff > 0 THEN 1 END),
  Draws  = COUNT(CASE WHEN ScoreDiff = 0 THEN 1 END),
  Losses = COUNT(CASE WHEN ScoreDiff < 0 THEN 1 END) 
FROM Matches
CROSS APPLY (VALUES
    (HomeTeam, HomeScore - AwayScore),
    (AwayTeam, AwayScore - HomeScore)
) v(Team, ScoreDiff)
GROUP BY
  v.Team;

Team	Wins	Draws	Losses
Botswana	0	1	0
DRC	0	1	0
France	1	1	0
USA	0	1	1

db<>fiddle