Does anyone know how to implement a Cross Table in MySql to help me? I need to transform a query that always returns 4 rows of result to only 1 row placing each row field in columns next to each other, as shown in the image below. NOTE: Queue field values can always vary.
V You can use several sub-selects for this, so I realized, the order of the columns is given by the date column, and always will be 4 columns. Here's how:
select distinct
h.ticket,
(select x.fila from historico x where x.ticket = h.ticket order by data limit 1) as fila_1,
(select x.data from historico x where x.ticket = h.ticket order by data limit 1) as data_fila_1,
(select x.fila from historico x where x.ticket = h.ticket order by data limit 1 offset 1) as fila_2,
(select x.data from historico x where x.ticket = h.ticket order by data limit 1 offset 1) as data_fila_2,
(select x.fila from historico x where x.ticket = h.ticket order by data limit 1 offset 2) as fila_3,
(select x.data from historico x where x.ticket = h.ticket order by data limit 1 offset 2) as data_fila_3,
(select x.fila from historico x where x.ticket = h.ticket order by data limit 1 offset 3) as fila_4,
(select x.data from historico x where x.ticket = h.ticket order by data limit 1 offset 3) as data_fila_4
from historico h
Result:
ticket fila_1 data_fila_1 fila_2 data_fila_2 fila_3 data_fila_3 fila_4 data_fila_4
61100 37 2018-06-01T10:00:00Z 12 2018-06-01T18:00:00Z 20 2018-06-04T09:00:00Z 35 2018-06-05T17:00:00Z
71100 37 2018-06-02T10:00:00Z 12 2018-06-02T18:00:00Z 20 2018-06-05T09:00:00Z 35 2018-06-06T17:00:00Z
I put it in SQLFiddle
Note: I inserted another example ticket.