Dynamically convert rows to columns in MariaDB 10.3

Question

I have the following table data:

teams	tournament	assoc	player_id
MT	101	EGY	100696
MT	101	EGY	100439
MT	101	EGY	102486
MT	101	EGY	111887
MT	101	NGR	113563
MT	101	NGR	111959
MT	101	NGR	145024
MT	101	NGR	104514
MT	101	NGR	112092
WT	101	EGY	202375
WT	101	EGY	116724
WT	101	EGY	134971
WT	101	EGY	200157
WT	101	NGR	102441
WT	101	NGR	146169
WT	101	NGR	134970
WT	101	NGR	133736
WT	101	NGR	101247

I would like to transpose rows to columns and get the following:

teams	tournament	assoc	player_id1	player_id2	player_id3	player_id4	player_id5
MT	101	EGY	100696	100439	102486	111887
MT	101	NGR	113563	111959	145024	104514	112092
WT	101	EGY	202375	116724	134971	200157
WT	101	NGR	102441	146169	134970	133736	101247

Needs to group by assoc, tournament and teams and max columns of player_idx can be 5. Tried solutions for Pivot tables with no success.

What version of MySQL are you using? I.e. what does `SELECT VERSION();` report? — Bill Karwin, Sep 01 '22 at 17:24
In a given tournament, how do you determine which player is player1, player2, etc.? — Bill Karwin, Sep 01 '22 at 17:25
I edited your title and tags, because you are using MariaDB, not MySQL. Although MariaDB started as a fork of MySQL in 2010, and they still have a lot of code in common, you should not think of MariaDB as the same as MySQL, or even guaranteed to be compatible anymore. Both products have advanced since the fork. — Bill Karwin, Sep 01 '22 at 17:54
@BillKarwin You are right. I should have mentioned MariaDB and not MySQL. Thanks for your help. It works like a charm. — dimoss, Sep 01 '22 at 19:21

score 1 · Accepted Answer · answered Sep 01 '22 at 17:53

with cte as (select teams, tournament, assoc, player_id, row_number() over (partition by teams, tournament, assoc) as rownum from mytable)
select teams, tournament, assoc,
  max(case rownum when 1 then player_id end) as player_id1,
  max(case rownum when 2 then player_id end) as player_id2,
  max(case rownum when 3 then player_id end) as player_id3,
  max(case rownum when 4 then player_id end) as player_id4,
  max(case rownum when 5 then player_id end) as player_id5
from cte
group by teams, tournament, assoc;

+-------+------------+-------+------------+------------+------------+------------+------------+
| teams | tournament | assoc | player_id1 | player_id2 | player_id3 | player_id4 | player_id5 |
+-------+------------+-------+------------+------------+------------+------------+------------+
| MT    |        101 | EGY   |     100439 |     100696 |     102486 |     111887 |       NULL |
| MT    |        101 | NGR   |     104514 |     111959 |     112092 |     113563 |     145024 |
| WT    |        101 | EGY   |     116724 |     134971 |     200157 |     202375 |       NULL |
| WT    |        101 | NGR   |     101247 |     102441 |     133736 |     134970 |     146169 |
+-------+------------+-------+------------+------------+------------+------------+------------+

Tested on MySQL 8.0, and on this dbfiddle on MariaDB 10.3:

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=ec2fc62b17fdc5eac9198f9298f229cc

Dynamically convert rows to columns in MariaDB 10.3

1 Answers1