CODEWITHSUNDEEP
mysqlsqldatabase
Iván Guerra
Iván Guerra

Reputation: 81

Turn MySQL column values from query to a row

I have the following query: 

mysql> select id_sucursal from dw_sucursallookup where sucursal = "Centro" 
           UNION ALL 
       select id_sexo from dw_sexolookup where sexo = "Hombre" 
           UNION ALL 
       select id_tiempo from dw_tiempolookup where fecha = "2018-06-27" and hora = "noche" 
           UNION ALL 
       select id_edad from dw_edadlookup where edad = "41-55" 
           UNION ALL 
       select id_tipo_comida from dw_tipo_comidalookup where tipo_de_comida = 'dulce';

Which have the following output: 

+-------------+
| id_sucursal |
+-------------+
|  2014820869 |
|  2127812561 |
|  2020742459 |
|    49527792 |
|    95944605 |
+-------------+

What i want to achieve is to transpose the result to this: 

+-------------+-------------+-------------+-------------+----------------+
| id_sucursal |   id_sexo   |  id_tiempo  |   id_edad   | id_tipo_comida |
+-------------+-------------+-------------+-------------+----------------+
|  2014820869 |  2127812561 |  2020742459 |    49527792 |       95944605 |
+-------------+-------------+-------------+-------------+----------------+

How can I do that in mysql? I tried to search and found some solutions, but none of them worked like i wanted.

Thanks in advance

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use select with scalar subqueries:

select (select id_sucursal from dw_sucursallookup where sucursal = 'Centro') as id_sucursal,
       (select id_sexo from dw_sexolookup where sexo = 'Hombre') as id_sexo, 
       (select id_tiempo from dw_tiempolookup where fecha = '2018-06-27' and hora = 'noche') as id_tiempo
       (select id_edad from dw_edadlookup where edad = '41-55') as id_edad,
       (select id_tipo_comida from dw_tipo_comidalookup where tipo_de_comida = 'dulce') as tipo_de_comida

Upvotes: 1

Related Questions