How to transfer the row of a table in column

Asked

Viewed 390 times

2

I’m breaking my head with a problem, I need the information of a column, built through query, to turn into line (query header).

I saw some tutorials and related debts but could not implement:

  • PIVOT - failed to implement in mysql;
  • CASE THEN - At first gave right, the problem is he kept doubling the lines by user,

inserir a descrição da imagem aqui

SELECT 
usuario,
op_nome,      
CASE WHEN (YEAR(cl_data_cadastro)='2016' AND MONTH(cl_data_cadastro) ='1') THEN cl_idade ELSE NULL END AS '01/2016',
CASE WHEN (YEAR(cl_data_cadastro)='2016' AND MONTH(cl_data_cadastro) ='2') THEN cl_idade ELSE NULL END AS '02/2016',
CASE WHEN (YEAR(cl_data_cadastro)='2016' AND MONTH(cl_data_cadastro) ='3') THEN cl_idade ELSE NULL END AS '03/2016'    

FROM    
    clientes 

WHERE
    cl_data_cadastro BETWEEN '2016-01-01' AND '2016-03-31'    
GROUP BY op_usuario, YEAR(ct_data_cadastro), MONTH(ct_data_cadastro)
mysql database
  • 1

    It is the same query !? there is only one age occurrence ? try something like max(CASE WHEN(YEAR(cl_data_registration)='2016' AND MONTH(cl_data_registration) ='3') THEN cl_idade ELSE NULL END) AS '03/2016' com GROUP BY

1 answer

1

Buddy, try applying a MAX in each column resulting from CASE WHEN.

Something like that: 

SELECT r.usuario,
       MAX(r.col1),
       MAX(r.col2),
       MAX(r.col3)
  FROM (SELECT 
        usuario,
        op_nome,      
        CASE WHEN (YEAR(cl_data_cadastro)='2016' AND MONTH(cl_data_cadastro) ='1') THEN cl_idade ELSE NULL END AS '01/2016',
        CASE WHEN (YEAR(cl_data_cadastro)='2016' AND MONTH(cl_data_cadastro) ='2') THEN cl_idade ELSE NULL END AS '02/2016',
        CASE WHEN (YEAR(cl_data_cadastro)='2016' AND MONTH(cl_data_cadastro) ='3') THEN cl_idade ELSE NULL END AS '03/2016'    

        FROM    
            clientes 

        WHERE
            cl_data_cadastro BETWEEN '2016-01-01' AND '2016-03-31'    
        GROUP BY op_usuario, YEAR(ct_data_cadastro), MONTH(ct_data_cadastro)) AS r
 GROUP BY r.usuario

Substitute co1, col2 and col3 by the names of their columns.

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.