MySQL Group by with named columns from grouped data

Question

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.

Does this answer your question? [MySQL pivot table query with dynamic columns](https://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) — Ankit Bajpai, Jan 09 '20 at 15:40
@AnkitBajpai kind of yes. It is similar to the answer from GMB however uses 2 tables but the selected answer in that provides further reading i can do to try learn some more myself. — CrystalShardz, Jan 09 '20 at 15:46
Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry, Jan 09 '20 at 15:53

GMB · Accepted Answer · 2020-01-09T15:43:03.797

1

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

edited Jan 09 '20 at 15:43

answered Jan 09 '20 at 15:39

GMB

216,147
25
84
135

This answer will not help OP as he already mention that no of games is dynamic as any no of games can be played. – Ankit Bajpai Jan 09 '20 at 15:41
@AnkitBajpai: I updated my answer to be explicit about that. – GMB Jan 09 '20 at 15:45
OP have already said that any number of games can be played. So YOu must provide the generic enough solution. – Ankit Bajpai Jan 09 '20 at 15:55
@AnkitBajpai the generic answer would include a potentially infinite list of columns. This would make the table unreadable! – Lelio Faieta Jan 09 '20 at 15:56
@LelioFaieta, Please see my answer, what I was trying to say. – Ankit Bajpai Jan 09 '20 at 16:07
@AnkitBajpai There are no obligations upon contributors to this forum – Strawberry Jan 09 '20 at 16:09
@Strawberry, I agree that there is no obligation, But it must be absolute responsibility of every contributor to provide most generic and correct solution as possible. – Ankit Bajpai Jan 10 '20 at 13:52
@AnkitBajpai No. That's simply not the case. – Strawberry Jan 10 '20 at 13:53
Ok, So can we provide any garbage here also? – Ankit Bajpai Jan 10 '20 at 13:54

score 1 · Answer 2 · answered Jan 09 '20 at 16:06

You can try below dynamic script to generate your desired result without knowing the no of games played already -

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(game = ''',
      game,
      ''', game, NULL)) AS game_',
      game
    )
  ) into @sql
FROM mytable;

set @sql = CONCAT('SELECT id, fixture_id, member_id, team_id, ', @sql, ' from mytable group by id, fixture_id, member_id, team_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is the demo.

MySQL Group by with named columns from grouped data

2 Answers2