funcion - select horizontal mysql
MySQL-Filas a columnas (10)
Traté de buscar publicaciones, pero solo encontré soluciones para SQL Server / Access. Necesito una solución en MySQL (5.X).
Tengo una tabla (llamada historial) con 3 columnas: hostid, itemname, itemvalue.
Si selecciono ( select * from history ), devolverá
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
+--------+----------+-----------+
| 1 | B | 3 |
+--------+----------+-----------+
| 2 | A | 9 |
+--------+----------+-----------+
| 2 | c | 40 |
+--------+----------+-----------+
¿Cómo consulto la base de datos para devolver algo así como
+--------+------+-----+-----+
| hostid | A | B | C |
+--------+------+-----+-----+
| 1 | 10 | 3 | 0 |
+--------+------+-----+-----+
| 2 | 9 | 0 | 40 |
+--------+------+-----+-----+
Aprovechando la idea de Matt Fenwick que me ayudó a resolver el problema (muchas gracias), reduzcamos a una sola consulta:
select
history.*,
coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid
Edito la respuesta de Agung Sagita de la subconsulta para unirme. No estoy seguro de cuánta diferencia hay entre esta 2 vía, pero solo para otra referencia.
SELECT hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME=''A''
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME=''B''
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME=''C''
Esta no es la respuesta exacta que está buscando, pero era una solución que necesitaba en mi proyecto y espero que esto ayude a alguien. Esto mostrará una lista de n elementos de fila separados por comas. Group_Concat hace esto posible en MySQL.
select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions
from cemetery
left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id
left join names on cemetery_names.name_id = names.name_id
left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id
left join
(
select
cemetery_contact.cemetery_id as cID,
group_concat(contacts.name, char(32), phone.number) as Contact_Info
from cemetery_contact
left join contacts on cemetery_contact.contact_id = contacts.contact_id
left join phone on cemetery_contact.contact_id = phone.contact_id
group by cID
)
as c on c.cID = cemetery.cemetery_id
left join
(
select
cemetery_id as dID,
group_concat(direction_type.direction_type) as Direction_Type,
group_concat(directions.value , char(13), char(9)) as Directions
from directions
left join direction_type on directions.type = direction_type.direction_type_id
group by dID
)
as d on d.dID = cemetery.cemetery_id
group by Cemetery_ID
Este cementerio tiene dos nombres comunes, por lo que los nombres se enumeran en diferentes filas conectadas por una única identificación, pero dos identificadores de nombre y la consulta produce algo como esto
CemeteryID Cemetery_Name Latitude
1 Appleton, Sulpher Springs 35.4276242832293
Intuyo una forma de hacer que mis informes conviertan las filas en columnas casi dinámicas usando consultas simples. Puedes verlo y probarlo en línea aquí .
El número de columnas de consulta es fijo, pero los valores son dinámicos y se basan en los valores de las filas. Puedes construirlo Entonces, uso una consulta para construir el encabezado de la tabla y otra para ver los valores:
SELECT distinct concat(''<th>'',itemname,''</th>'') as column_name_table_header FROM history order by 1;
SELECT
hostid
,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '''' end) as col1
,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '''' end) as col2
,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '''' end) as col3
,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '''' end) as col4
FROM history order by 1;
Puedes resumirlo también:
SELECT
hostid
,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
FROM history group by hostid order by 1;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | NULL |
| 2 | 9 | NULL | 40 |
+--------+------+------+------+
Resultados de RexTester :
http://rextester.com/ZSWKS28923
Para un ejemplo real de uso, este informe a continuación muestra en columnas las horas de llegadas de salidas de bote / autobús con un horario visual. Verá una columna adicional no utilizada en la última columna sin confundir la visualización: ** sistema de emisión de boletos para vender boletos en línea y presenciales
Lo hago en Group By hostId luego solo se muestra la primera fila con valores,
me gusta:
A B C
1 10
2 3
Mi solución :
select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as C from (
select
hostid,
case when itemName = ''A'' then itemvalue end as A,
case when itemName = ''B'' then itemvalue end as B,
case when itemName = ''C'' then itemvalue end as C
from history
) h group by hostid
Produce los resultados esperados en el caso presentado.
Otra opción, especialmente útil si tiene muchos elementos que necesita pivotar, es dejar que mysql genere la consulta por usted:
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
''ifnull(SUM(case when itemname = '''''',
itemname,
'''''' then itemvalue end),0) AS `'',
itemname, ''`''
)
) INTO @sql
FROM
history;
SET @sql = CONCAT(''SELECT hostid, '', @sql, ''
FROM history
GROUP BY hostid'');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FIDDLE Agregó algunos valores extra para verlo funcionar
GROUP_CONCAT tiene un valor predeterminado de 1000, así que si tienes una consulta realmente grande, cambia este parámetro antes de ejecutarlo
SET SESSION group_concat_max_len = 1000000;
Prueba:
DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);
INSERT INTO history VALUES(1,''A'',10),(1,''B'',3),(2,''A'',9),
(2,''C'',40),(2,''D'',5),
(3,''A'',14),(3,''B'',67),(3,''D'',8);
hostid A B C D
1 10 3 0 0
2 9 0 40 5
3 14 67 0 8
Voy a agregar una explicación más larga y detallada de los pasos a seguir para resolver este problema. Me disculpo si es demasiado largo.
Comenzaré con la base que has dado y la usaré para definir un par de términos que usaré para el resto de esta publicación. Esta será la tabla base :
select * from history;
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
| 1 | B | 3 |
| 2 | A | 9 |
| 2 | C | 40 |
+--------+----------+-----------+
Este será nuestro objetivo, la bonita mesa pivote :
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
Los valores en la columna history.hostid se convertirán en valores y en la tabla dinámica. Los valores en la columna history.itemname se convertirán en x-values (por razones obvias).
Cuando tengo que resolver el problema de crear una tabla dinámica, la abordo usando un proceso de tres pasos (con un cuarto paso opcional):
- seleccione las columnas de interés, es decir , valores y y valores x
- Extienda la tabla base con columnas adicionales, una para cada valor x
- agrupar y agregar la tabla extendida, un grupo por cada valor y
- (opcional) embellecer la tabla agregada
Vamos a aplicar estos pasos a su problema y veamos qué obtenemos:
Paso 1: selecciona columnas de interés . En el resultado deseado, hostid proporciona los valores y y itemname proporciona los valores x .
Paso 2: extiende la tabla base con columnas adicionales . Por lo general, necesitamos una columna por valor x. Recuerde que nuestra columna x-value es itemname :
create view history_extended as (
select
history.*,
case when itemname = "A" then itemvalue end as A,
case when itemname = "B" then itemvalue end as B,
case when itemname = "C" then itemvalue end as C
from history
);
select * from history_extended;
+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A | B | C |
+--------+----------+-----------+------+------+------+
| 1 | A | 10 | 10 | NULL | NULL |
| 1 | B | 3 | NULL | 3 | NULL |
| 2 | A | 9 | 9 | NULL | NULL |
| 2 | C | 40 | NULL | NULL | 40 |
+--------+----------+-----------+------+------+------+
Tenga en cuenta que no cambiamos el número de filas, solo agregamos columnas adicionales. También tenga en cuenta el patrón de NULL s: una fila con itemname = "A" tiene un valor no nulo para la nueva columna A y valores nulos para las otras columnas nuevas.
Paso 3: agrupe y agregue la tabla extendida . Necesitamos group by hostid , ya que proporciona los valores y:
create view history_itemvalue_pivot as (
select
hostid,
sum(A) as A,
sum(B) as B,
sum(C) as C
from history_extended
group by hostid
);
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | NULL |
| 2 | 9 | NULL | 40 |
+--------+------+------+------+
(Tenga en cuenta que ahora tenemos una fila por valor y). De acuerdo, ya casi llegamos. Solo tenemos que deshacernos de esos feos NULL .
Paso 4: embellecer Simplemente vamos a reemplazar cualquier valor nulo con ceros para que el conjunto de resultados sea más agradable de ver:
create view history_itemvalue_pivot_pretty as (
select
hostid,
coalesce(A, 0) as A,
coalesce(B, 0) as B,
coalesce(C, 0) as C
from history_itemvalue_pivot
);
select * from history_itemvalue_pivot_pretty;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
Y hemos terminado: construimos una bonita y bonita tabla dinámica utilizando MySQL.
Consideraciones al aplicar este procedimiento:
- qué valor usar en las columnas adicionales.
itemvalueen este ejemplo - qué valor "neutral" usar en las columnas adicionales. Utilicé
NULL, pero también podría ser0o"", dependiendo de su situación exacta - qué función agregada usar al agrupar. Utilicé la
sum, pero elcounty elmaxtambién se usan con frecuencia (elmaxse utiliza a menudo al construir "objetos" de una sola fila que se han distribuido en muchas filas) - usando columnas múltiples para valores y. Esta solución no se limita a usar una sola columna para los valores y, simplemente conecte las columnas adicionales en la cláusula
group by(y no olvideselect)
Limitaciones conocidas
- esta solución no permite n columnas en la tabla dinámica; cada columna dinámica debe agregarse manualmente al extender la tabla base. Entonces, para 5 o 10 valores x, esta solución es agradable. Para 100, no tan agradable. Hay algunas soluciones con procedimientos almacenados que generan una consulta, pero son feas y difíciles de corregir. Actualmente no sé de una buena manera de resolver este problema cuando la tabla pivote necesita tener muchas columnas.
usar subconsulta
SELECT hostid,
(SELECT VALUE FROM TableTest WHERE ITEMNAME=''A'' AND hostid = t1.hostid) AS A,
(SELECT VALUE FROM TableTest WHERE ITEMNAME=''B'' AND hostid = t1.hostid) AS B,
(SELECT VALUE FROM TableTest WHERE ITEMNAME=''C'' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid
pero será un problema si la sub consulta que resulta más de una fila, usa más funciones agregadas en la subconsulta
SELECT
hostid,
sum( if( itemname = ''A'', itemvalue, 0 ) ) AS A,
sum( if( itemname = ''B'', itemvalue, 0 ) ) AS B,
sum( if( itemname = ''C'', itemvalue, 0 ) ) AS C
FROM
bob
GROUP BY
hostid;