Skip to content
Advertisement

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:

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:

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

Advertisement

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):

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
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement