How to transpose the row of a column table

Question

How to transpose the row of a column table

Navigation

#1 by (1 votes)

2

I'm breaking my head with a problem, I need the information from a column, constructed through query, to be transformed into a line (query header).

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

PIVOT - I could not implement mysql;
CASE THEN - Initially gave right, the problem is that he continued to duplicate the lines by user,

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

asked by anonymous 01.06.2016 / 23:31

1 answer

Access controller of another project in the same solution C ++ execute secondary program, only if the command is entered

score 1 · Answer 1

Friend, try to apply a MAX to each resulting column of CASE WHEN .

Something like this:

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

Replace co1 , col2 , and col3 with the names of your columns.