sql - Cómo pivotar filas a columnas dinámicamente en mysql
Estoy enfrentando un problema y creo que tengo que usar pivot para resolverlo, con el que no estoy familiarizado. Estaría muy agradecido si alguien me pudiera ayudar.
La siguiente consulta da como resultado el siguiente resultado. Por supuesto, esto es correcto.
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
Sin embargo, quiero que la mesa se vea así. ¿Cómo tengo que adaptar la consulta? Es posible que el rango 2 pueda albergar una cantidad ilimitada de jugadores. Los rangos 1, 3, 4 y 5 tienen un límite de 1.
equipo 1 2 2 2 3 4 5 1 1 jugador 1 1 jugador 2 1 jugador 3 1 jugador 4 1 jugador 5 1 jugador 6 1 jugador 7 2 2jugador1 2jugador2 2jugador3 2jugador4 3 3Pcapa1 3jugador2 3jugador3 3jugador4 3 jugadores 5 4 4 jugadores 1 4 jugadores 2 4jugador3 3jugador4Agradezco cualquier ayuda. ¡Muchas gracias!
¿Qué dbms estás usando?
- Kazi Mohammad Ali Nur Romel28/03/2021 a las 14:51
Estoy usando MySQL
- Sólo Sarah28 de marzo de 2021 a las 14:59
¿Qué intentaste tú mismo? Hay un ejemplo bastante bueno en los documentos (si usa MSSQL, consulte: learn.microsoft.com/en-us/sql/t-sql/queries/…
- Luuk28/03/2021 a las 14:59
y para MySQL, el ejemplo está aquí: stackoverflow.com/questions/7674786/…
- Luuk28/03/2021 a las 15:00
Hola Luuk, gracias por los ejemplos. Solo soy un usuario y estoy tratando de obtener los datos a través de la base de datos para no tener que obtenerlos de las tablas del frontend. Tengo más de 500 filas que copié, pegué y ordené manualmente. Simplemente lleva demasiado tiempo y no estoy muy familiarizado con el pivote, pero lo profundizaré.
- Sólo Sarah28/03/2021 a las 15:09
------------------------------------
Para darle otro ejemplo: 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;
salida:
equipo 1 2 3 4 5 6 7 1 1 jugador 1 1 jugador 2 1 jugador 3 1 jugador 41 jugador 5 1 jugador 6 1 jugador 7 2 2jugador1 2jugador2 2jugador3 2jugador4EDITAR: Obtener el grupo 2 en 1 columna (solo apliqué el cambio para el rango==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;
1
Oye, gracias por eso. Esto ayuda mucho. ¿Sabes cómo puedo mostrar los nombres cuando 3 jugadores tienen el rango 2? Por el momento sólo muestra el primer jugador con rango dos y no elsegundo o tercero.
- Sólo Sarah28/03/2021 a las 16:41
------------------------------------
MySQL no admite el operador relacional PIVOT. Pero seguramente hay una manera de pivotar los datos en MySQL. El desafío en sus datos era la falta de unicidad de las columnas dinámicas. Habría varias columnas con el nombre "2" en su salida.
Así que he creado unicidad concatenando los últimos tres equipos después del campo de nombre. Comparto aquí el pivote estático y dinámico para sus datos. En el pivote dinámico, no importa si tienes tres jugadores o diez, entregará tus datos en el formato requerido. Pero si tu lista de equipo está arreglada junto con platu lista, entonces puedes optar por un pivote estático ya que será más rápido.
En pivote dinámico he creado tus datos como tabla t. Puede colocar su consulta de selección completa en lugar de t.
Esquema:
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);
Consulta nº 1 (pivote estático)
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
Consulta nº 2 (pivote dinámico)
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;
Salida:
equipo 1 2 2 2 3 4 5 1 11Jugador1 21Jugador2 21Jugador3 21Jugador4 31Jugador5 41Jugador6 51Jugador7 2 12Jugador1 22Jugador2 22Jugador3 22Jugador4 nulo nulo nulodb<>juega aquí