How to pivot rows to column dynamically in mysql

Question

I'm facing a problem and I think I have to use pivot to solve it which I am not familiar with. I would be very thankful if someone would provide me help.

The following query results in the following output. This is of course correct.

SELECT tm.team_id AS team, pp.user_name AS name, tm.rank AS rank
FROM teams_members AS tm 
    LEFT JOIN players_profiles AS pp
    ON pp.uid=tm.user_id
WHERE tm.team_id = 1 OR tm.team_id = 2
ORDER BY  tm.team_id, tm.rank

team	name	rank
1	1Player1	1
1	1Player2	2
1	1Player3	2
1	1Player4	2
1	1Player5	3
1	1Player6	4
1	1Player7	5
2	2Player1	1
2	2Player2	2
2	2Player3	2
2	2Player4	2

I want the table to look like this though. How do I have to adapt the query? It is possible, that rank 2 can hold an unlimited amount of players. Rank 1,3,4 and 5 are set to a limit of 1.

team	1	2	2	2	3	4	5
1	1Player1	1Player2	1Player3	1Player4	1Player5	1Player6	1Player7
2	2Player1	2Player2	2Player3	2Player4
3	3Player1	3Player2	3Player3	3Player4			3Player5
4	4Player1	4Player2			4Player3		3Player4

I appreciate any help. Thanks a lot!

What did you try yourself? There is a pretty good example for it in the docs (if you use MSSQL, see: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15 — Luuk, Mar 28 '21 at 14:59
and for MySQL, the example is here: https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — Luuk, Mar 28 '21 at 15:00
Hi Luuk, thanks for the examples. I'm just a user and I'm trying to get the data via db so that I do not have to get them from the frontend tables. I've got over 500 rows which I manually copy pasted and sorted. It just takes too much time and I'm not that familiar with pivot, but will look deeper into it. — JustSarah, Mar 28 '21 at 15:09
Hey Luuk, no. Unfortunately not. I just edited the main post. — JustSarah, Mar 28 '21 at 16:42

Luuk · Answer 1 · 2021-03-28T16:55:16.490

To give you another example: DBFIDDLE

DROP TABLE IF EXISTS testPivot ;
CREATE TABLE testPivot(i int, name varchar(20), k int);

INSERT INTO testPivot VALUES
   (1,'1Player1',1),
   (1,'1Player2',2),
   (1,'1Player3',2),
   (1,'1Player4',2),
   (1,'1Player5',3),
   (1,'1Player6',4),
   (1,'1Player7',5),
   (2,'2Player1',1),
   (2,'2Player2',2),
   (2,'2Player3',2),
   (2,'2Player4',2);
   
   
SELECT
  i as team,
  max(case when right(name,1)='1' then name else '' end) as '1',
  max(case when right(name,1)='2' then name else '' end) as '2',
  max(case when right(name,1)='3' then name else '' end) as '3',
  max(case when right(name,1)='4' then name else '' end) as '4',
  max(case when right(name,1)='5' then name else '' end) as '5',
  max(case when right(name,1)='6' then name else '' end) as '6',
  max(case when right(name,1)='7' then name else '' end) as '7'
FROM testPivot
GROUP BY i;

output:

team	1	2	3	4	5	6	7
1	1Player1	1Player2	1Player3	1Player4	1Player5	1Player6	1Player7
2	2Player1	2Player2	2Player3	2Player4

EDIT: The get group 2 in 1 column (I only applied the change for rank==2), use:

SELECT
  i as team,
  max(case when right(name,1)='1' then name else '' end) as '1',
  regexp_replace(regexp_replace(regexp_replace(group_concat(case when k=2 then name else '' end),'[,]+',','),'^,',''),',$','') as '2',
  -- max(case when right(name,1)='2' then name else '' end) as '2',
  max(case when right(name,1)='3' then name else '' end) as '3',
  max(case when right(name,1)='4' then name else '' end) as '4',
  max(case when right(name,1)='5' then name else '' end) as '5',
  max(case when right(name,1)='6' then name else '' end) as '6',
  max(case when right(name,1)='7' then name else '' end) as '7'
FROM testPivot
GROUP BY i;

team	1	2	3	4	5	6	7
1	1Player1	1Player2,1Player3,1Player4	1Player3	1Player4	1Player5	1Player6	1Player7
2	2Player1	2Player2,2Player3,2Player4	2Player3	2Player4

Hey, thanks for that. It helps a lot. Do you know how I can display the names, when 3 players have rank 2? At the moment it only display the first player with rank two and not the second or third. — JustSarah, Mar 28 '21 at 16:41

Kazi Mohammad Ali Nur Romel · Answer 2 · 2021-03-28T17:03:20.607

MySQL does not support PIVOT relational operator. But surely there is way to pivot data in MySQL. The challenge in your data was non uniqueness of pivot columns. There would be multiple columns with name "2" in your output.

So I have created uniqueness by concatenating team last three later of name field. I am sharing here both static and dynamic pivoting for your data. In dynamic pivot it would not matter if you have three players or ten it will deliver your data in your required format. But if your team list is fixed along with player list then you can go for static pivot since it will be faster.

In dynamic pivot I have created your data as table t. You can put your entire select query in place of t.

Schema:

 create table t (team   int, name   varchar(50) ,rank int);
 insert into t values(1,    '1Player1', 1);
 insert into t values(1,    '1Player2', 2);
 insert into t values(1,    '1Player3', 2);
 insert into t values(1,    '1Player4', 2);
 insert into t values(1,    '1Player5', 3);
 insert into t values(1,    '1Player6', 4);
 insert into t values(1,    '1Player7', 5);
 insert into t values(2,    '2Player1', 1);
 insert into t values(2,    '2Player2', 2);
 insert into t values(2,    '2Player3', 2);
 insert into t values(2,    '2Player4', 2);

Query#1 (static pivot)

   SELECT team, MAX(IF(concat(rank,right(name,3)) = '1er1', concat(rank,name), NULL)) AS "1",
    MAX(IF(concat(rank,right(name,3)) = '2er2', concat(rank,name), NULL)) AS "2",
    MAX(IF(concat(rank,right(name,3)) = '2er3', concat(rank,name), NULL)) AS "2",
    MAX(IF(concat(rank,right(name,3)) = '2er4', concat(rank,name), NULL)) AS "2",
    MAX(IF(concat(rank,right(name,3)) = '3er5', concat(rank,name), NULL)) AS "3",
    MAX(IF(concat(rank,right(name,3)) = '4er6', concat(rank,name), NULL)) AS "4",
    MAX(IF(concat(rank,right(name,3)) = '5er7', concat(rank,name), NULL)) AS "5" 
    FROM (SELECT tm.team_id AS team, pp.user_name AS name, tm.rank AS rank
FROM teams_members AS tm 
    LEFT JOIN players_profiles AS pp
    ON pp.uid=tm.user_id
WHERE tm.team_id = 1 OR tm.team_id = 2
ORDER BY  tm.team_id, tm.rank)t
    GROUP BY team

Query#2 (dynamic pivot)

 SET @sql = NULL;
 SELECT
   GROUP_CONCAT(DISTINCT
     CONCAT(
       'MAX(IF(concat(rank,right(name,3)) = ''',
       concat(rank,right(name,3)),
       ''', concat(rank,name), NULL)) AS ',
       concat('"',rank,'"')
     )
   ) INTO @sql
 FROM t;
 
 SET @sql = CONCAT('SELECT team
                     , ', @sql, ' 
                    FROM t
                    GROUP BY team');
 
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

Output:

team	1	2	2	2	3	4	5
1	11Player1	21Player2	21Player3	21Player4	31Player5	41Player6	51Player7
2	12Player1	22Player2	22Player3	22Player4	null	null	null

db<>fiddle here

How to pivot rows to column dynamically in mysql

2 Answers2