MySQL - Convert order lines to columns

Question

I'm trying to convert the result I get from a query but I'm totally lost.

I'm fetching data from an orders table, the sql looks like:

select 
ped_pedidos.id_escuela, 
escuelas.nombre, 
provincias.descripcion, 
if(ped_pedidos.id_tipo = 3, ped_pedidos.total_pedido*-1, ped_pedidos.total_pedido) as total, 
ped_productos.descripcion, 
ped_pedidos_lineas.cantidad as t_pedido 
from ped_pedidos 
left join escuelas on ped_pedidos.id_escuela = escuelas.id 
left join provincias on escuelas.id_provincia = provincias.id
left join ped_pedidos_lineas on ped_pedidos_lineas.id_pedido = ped_pedidos.id 
left join ped_productos on ped_productos.id = ped_pedidos_lineas.id_producto
where ped_pedidos.curso = '2017' and ped_pedidos.id_estado = 2
group by ped_pedidos.id

And I'm getting the following data:

+------------+--------+-------------+----------+-------------------+----------+--+
| id_escuela | nombre | descripcion | total    | descripcion       | cantidad |  |
+------------+--------+-------------+----------+-------------------+----------+--+
| 628        | fake 1 | Girona      | 23726.50 | Product Wonder 1  | 54       |  |
| 613        | fake 2 | Granada     | 1934.50  | Product Great 2   | 26       |  |
| 457        | fake 3 | Barcelona   | 2210.00  | Product Awesome 3 | 65       |  |
| 628        | fake 1 | Girona      | 23726.50 | Product Great 2   | 15       |  |
+------------+--------+-------------+----------+-------------------+----------+--+

I would like to convert this to the following:

+------------+--------+-------------+----------+------------------+-----------------+-------------------+
| id_escuela | nombre | descripcion | total    | Product Wonder 1 | Product Great 2 | Product Awesome 3 |
+------------+--------+-------------+----------+------------------+-----------------+-------------------+
| 628        | fake 1 | Girona      | 23726.50 | 54               | 15              | 0                 |
| 613        | fake 2 | Granada     | 1934.50  | 0                | 26              | 0                 |
| 457        | fake 3 | Barcelona   | 2210.00  | 0                | 0               | 65                |
+------------+--------+-------------+----------+------------------+-----------------+-------------------+

Is it possible or I'm trying to do something that can't be done?

This is called a pivot table and this question has been asked and answered here on SO several times already. The duplicate link describes both the static (column names are known in advance) and dynamic (column names are determined on the fly) pivoting within MySQL. However, pls consider performing such transformations in the application logic, not in mysql, since it may be more efficient. — Shadow, May 11 '17 at 16:24
Many thanks @Shadow. There is no application logic, we're trying to extract raw data form the database without any interface within... so this is why I'm trying to apply this "pivot table". Regards — Raimon Martin, May 15 '17 at 13:29
Then you may want to use some BI tools that can do this without you needing to code it. Anyway, you are given the link how to do achieve pivot tables from MySQL. — Shadow, May 15 '17 at 15:34

MySQL - Convert order lines to columns

0 Answers0