Pivot sets of two rows into a column

Question

I have a database of games between teams structured like so (simplified):

And I want to know the score of all the games played so far. I can achieve getting the scores of matches with:

SELECT match.home, match.away, player.team, COUNT(*) FROM match
    JOIN goal ON match.id = goal.match
    JOIN player ON goal.scorer = player.id
    
    WHERE match.id = 1 GROUP BY player.team;  -- Specific match
    GROUP BY match.id, player.team;           -- All matches

Home | Away | Team  | Count(*)
-----+------+-------+---------
T1   | T2   | T1    | 3
T1   | T2   | T2    | 1
T3   | T1   | T3    | 0
T3   | T1   | T1    | 2

But there are two problems: if one or both of the teams have not scored, the table doesn't show the team name and zero. I tried to right join the player to goal, but turns out they are not supported in SQLite. But this is the lesser problem.

I'd like to fetch information about all the games played so far in the form:

MatchID | Home | Away | HomeScore | AwayScore
--------+------+------+-----------+----------
1       | T1   | T2   | 3         | 1
2       | T3   | T1   | 0         | 2

And I believe this question or this one could be the answer, but I'm not sure how to start applying to my case. I am aware, that this might be achievec using Python or other external tools, but I'm looking for an SQL solution. Any help would be greatly appreciated!

Fiddle link

Here's a link to an SQL Fiddle: Fiddle me this

score 0 · Accepted Answer · answered Mar 05 '18 at 00:22

Consider joining a Home aggregate resultset and an Away aggregate resultset each left joined to Match to return all matches including non-scoring ones.

SELECT m.MatchID, m.Home, m.Away, 
       IFNULL(h.Score, 0) AS HomeScore, IFNULL(a.Score, 0) AS AwayScore
FROM 
    Match m
LEFT JOIN
   (SELECT m.MatchID, m.Home, Count(*) As Score 
    FROM Match m
    LEFT JOIN Goal g ON m.MatchID = g.Match 
    INNER JOIN Player p ON g.Player = p.PlayerID AND p.Team = m.Home
   GROUP BY m.MatchID, m.Home) As h 

ON m.MatchID = h.MatchID

LEFT JOIN
   (SELECT m.MatchID, m.Away, Count(*) As Score 
    FROM Team t
    INNER JOIN Match m ON t.Name = m.Away
    LEFT JOIN Goal g ON m.MatchID = g.Match 
    INNER JOIN Player p ON g.Player = p.PlayerID AND p.Team = t.Name
   GROUP BY m.MatchID, m.Away) As a

ON m.MatchID = a.MatchID

SQL Fiddle Demo

Pivot sets of two rows into a column

Fiddle link

1 Answers1