MySQL Group by with named columns from grouped data

I’m writing an application to record 10 pin bowling game results. I’ve done the submission bit but I’m stuck on displaying the results. Here is the structure:

id fixture_id member_id team_id game score
 1          1         1       1    1   189
 2          1         1       1    2   167
 3          1         1       1    3   190
 4          1         2       2    1   100
 5          1         2       2    2   167
 6          1         2       2    3   158

In the example above I am assuming each person played 3 games, however any number of games could be played.

How can I get the data in a more friendly format?

For example:

id - fixture_id - member_id - team_id - game_1 - game_2 - game_3

where columns game_1, game_2, and game_3 (and so forth) correspond to the score for that game.

Answer

You can pivot with conditional aggregation, but that requires that you know in advance how many columns you want to generate. The below solution handles up to 3 games per user (you can extend the select clause with more max(case ...) expressions to handle more than that):

select  
    fixture_id,
    member_id,
    team_id,
    max(case when game = 1 then score end) game_1,
    max(case when game = 2 then score end) game_2,
    max(case when game = 3 then score end) game_3
from mytable
group by 
    fixture_id,
    member_id,
    team_id

Demo on DB Fiddle:

fixture_id | member_id | team_id | game_1 | game_2 | game_3
---------: | --------: | ------: | -----: | -----: | -----:
         1 |         1 |       1 |    189 |    167 |    190
         1 |         2 |       2 |    100 |    167 |    158