mysql - Query to convert columns into rows

Question

mysql - Query to convert columns into rows

Navigation

#1 by (1 votes)

1

I have my query:

SELECT inscricao, assiduos_a1, assiduos_a2, max(data_inclusao)
  FROM cli_agendados_assiduos
 WHERE INSCRICAO = 10357410;

That returns:

inscricao   assiduos_a1 assiduos_a2 max(data_inclusao)
10357410    337         508         08/01/2015 14:54:48

But I need the results in lines:

Label       Valor   Inscrição   max(data_inclusao)
assiduos_a1 337     10357410    08/01/2015 14:54
assiduos_a2 508     10357410    08/01/2015 14:54

only this way my pie chart google will work correctly

mysql google-charts

asked by anonymous 06.02.2015 / 12:54

1 answer

How to return all records in a table Avoid overloading attributes when using magic methods

score 1 · Answer 1

I could only think of something like:

SELECT *
FROM (
    SELECT 'assiduos_a1' AS 'Label', assiduos_a1 AS 'Valor', inscricao, max(data_inclusao)
    FROM cli_agendados_assiduos
    UNION
    SELECT 'assiduos_a2' AS 'Label', assiduos_a2 AS 'Valor', inscricao, max(data_inclusao)
    FROM cli_agendados_assiduos
) a
WHERE inscricao = 10357410;

But I think you'd better rethink the structure of your table. Search for "Pivot tables".