CODEWITHSUNDEEP
mariadbpivot-tablemariadb-10.3
dimoss
dimoss

Reputation: 489

Dynamically convert rows to columns in MariaDB 10.3

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.

Upvotes: 2

Views: 769

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562348

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

Upvotes: 2

Related Questions