MySQL – Linhas para Colunas

Tentei pesquisar posts, mas só encontrei soluções para o SQL Server / Access. Eu preciso de uma solução no MySQL (5.X).

Eu tenho uma tabela (chamada histórico) com 3 colunas: hostid, itemname, itemvalue.
Se eu fizer um select ( select * from history ), ele retornará

  +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ | 1 | A | 10 | +--------+----------+-----------+ | 1 | B | 3 | +--------+----------+-----------+ | 2 | A | 9 | +--------+----------+-----------+ | 2 | c | 40 | +--------+----------+-----------+ 

Como faço para consultar o database para retornar algo como

  +--------+------+-----+-----+ | hostid | A | B | C | +--------+------+-----+-----+ | 1 | 10 | 3 | 0 | +--------+------+-----+-----+ | 2 | 9 | 0 | 40 | +--------+------+-----+-----+ 

Vou adicionar uma explicação um pouco mais longa e detalhada sobre as etapas a serem tomadas para resolver esse problema. Peço desculpas se for muito longo.


Vou começar com a base que você deu e usá-la para definir alguns termos que usarei para o restante deste post. Esta será a tabela base :

 select * from history; +--------+----------+-----------+ | hostid | itemname | itemvalue | +--------+----------+-----------+ | 1 | A | 10 | | 1 | B | 3 | | 2 | A | 9 | | 2 | C | 40 | +--------+----------+-----------+ 

Este será o nosso objective, a bonita tabela dinâmica :

 select * from history_itemvalue_pivot; +--------+------+------+------+ | hostid | A | B | C | +--------+------+------+------+ | 1 | 10 | 3 | 0 | | 2 | 9 | 0 | 40 | +--------+------+------+------+ 

Os valores na coluna history.hostid se tornarão valores y na tabela dinâmica. Os valores na coluna history.itemname se tornarão valores x (por razões óbvias).


Quando eu tenho que resolver o problema de criar uma tabela dinâmica, eu resolvo isso usando um processo de três etapas (com um quarto passo opcional):

  1. selecione as colunas de interesse, ou seja , valores y e valores x
  2. estender a tabela base com colunas extras – uma para cada valor x
  3. agrupar e agregar a tabela estendida – um grupo para cada valor y
  4. (opcional) embelezar a tabela agregada

Vamos aplicar estas etapas ao seu problema e ver o que recebemos:

Etapa 1: selecione as colunas de interesse . No resultado desejado, o hostid fornece os valores y e o nome do itemname fornece os valores x .

Etapa 2: estenda a tabela base com colunas extras . Normalmente, precisamos de uma coluna por valor x. Lembre-se de que nossa coluna de valor x é nome do 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 | +--------+----------+-----------+------+------+------+ 

Observe que não alteramos o número de linhas. Acabamos de adicionar colunas extras. Observe também o padrão de NULL s – uma linha com nome do itemname = "A" tem um valor não nulo para a nova coluna A e valores nulos para as outras novas colunas.

Etapa 3: agrupe e agregue a tabela estendida . Precisamos group by hostid , pois fornece os 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 | +--------+------+------+------+ 

(Note que agora temos uma linha por valor y). Ok, estamos quase lá! Nós só precisamos nos livrar daqueles feios NULL s.

Etapa 4: embelezar . Vamos apenas replace quaisquer valores nulos por zeros, então o resultado é melhor:

 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 | +--------+------+------+------+ 

E terminamos – construímos uma tabela dinâmica usando o MySQL.


Considerações ao aplicar este procedimento:

  • Qual valor usar nas colunas extras. Eu usei itemvalue neste exemplo
  • qual valor “neutro” para usar nas colunas extras. Eu usei NULL , mas também pode ser 0 ou "" , dependendo da sua situação exata
  • qual function agregada usar quando agrupar. Eu usei sum , mas count e max também são usados ​​com frequência (o max é frequentemente usado na criação de “objects” de uma linha que foram espalhados por várias linhas)
  • usando várias colunas para valores y. Esta solução não se limita a usar uma única coluna para os valores y – apenas conecte as colunas extras na cláusula group by (e não esqueça de select las)

Limitações Conhecidas:

  • essa solução não permite n colunas na tabela dinâmica – cada coluna dinâmica precisa ser adicionada manualmente ao estender a tabela base. Então, para 5 ou 10 valores de x, essa solução é legal. Por 100, não é tão legal. Existem algumas soluções com stored procedures gerando uma consulta, mas elas são feias e difíceis de acertar. Eu atualmente não sei de uma boa maneira de resolver este problema quando a tabela dinâmica precisa ter muitas colunas.
 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; 

Aproveitando a ideia de Matt Fenwick que me ajudou a resolver o problema (muito obrigado), vamos reduzí-lo a apenas uma 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 

Outra opção, especialmente útil se você tiver muitos itens que precisa girar, é deixar o mysql construir a consulta para você:

 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 Adicionado alguns valores extras para vê-lo funcionando

GROUP_CONCAT tem um valor padrão de 1000, portanto, se você tiver uma consulta realmente grande, altere esse parâmetro antes de executá-lo

 SET SESSION group_concat_max_len = 1000000; 

Teste:

 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 ABCD 1 10 3 0 0 2 9 0 40 5 3 14 67 0 8 

Eu edito a resposta de Agung Sagita da subconsulta para participar. Eu não tenho certeza sobre quanta diferença entre este 2 caminho, mas apenas para outra referência.

 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' 

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 

mas será um problema se a subconsulta resultar em mais de uma linha, usar mais uma function agregada na subconsulta

Eu faço isso em Group By hostId então ele mostrará apenas a primeira linha com valores,
gostar:

 ABC 1 10 2 3 

Eu descubro uma maneira de tornar meus relatórios convertendo linhas em colunas quase dinâmicas usando consultas simples. Você pode ver e testar on – line aqui .

O número de colunas de consulta é fixo, mas os valores são dynamics e baseados em valores de linhas. Você pode construí-lo Então, eu uso uma consulta para construir o header da tabela e outra para ver os valores:

 SELECT distinct concat('',itemname,'') 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; 

Você pode resumir também:

 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 do RexTester :

Resultados do RexTester

http://rextester.com/ZSWKS28923

Para um exemplo real de uso, este relatório abaixo mostra em colunas as horas de chegada das embarcações / ônibus com uma programação visual. Você verá uma coluna adicional não usada no ultimo col sem confundir a visualização: sistema de vendas online e consumidor final e controle de frota - xsl tecnologia - xsl.com.br ** sistema de emissão de bilhetes para venda de bilhetes online e presenciais

Esta não é a resposta exata que você está procurando, mas foi uma solução que eu precisava no meu projeto e espero que isso ajude alguém. Isso listará de 1 a n itens da linha separados por vírgulas. Group_Concat torna isso possível no 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 cemitério tem dois nomes comuns, então os nomes são listados em linhas diferentes conectadas por um único id, mas dois ids de nome e a consulta produz algo como isto

CemeteryID Cemetery_Name Latitude
1 Appleton, Sulpher Springs 35.4276242832293

Minha solução:

 select h.hostid, sum(ifnull(hA,0)) as A, sum(ifnull(hB,0)) as B, sum(ifnull(hC,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 

Produz os resultados esperados no caso submetido.