Cross Table in Mysql

Asked

Viewed 136 times

0

Does anyone know how to implement a Cross Table in Mysql to help me? I need to transform a query that always returns 4 result lines to only 1 row by placing each row field in columns next to each other, as illustrated in the image below. NOTE: Queue field values can always vary.

inserir a descrição da imagem aqui

mysql join

  • fila_1, fila_2.... only 4 rows or is variable as well ?

  • Only 4 rows, but there may be cases where there are only 3 rows. The idea is to make a calculation of how long each ticket was in each queue, I already created a Function to make this calculation, but the parameters to be passed to Function is the ticket number and the queue number, hence the return of Function will be the time between a queue and another, for example: 02:00:00 so I need the result to be all in one row, same in table 2.

  • The order of records you mean? There is the id field that was omitted in table 1, just consider that each row has a unique id, tried with subselect but unsuccessfully.

  • @Rovann Linhalis numbers 37, 12, 20 and 35 are the Queue Ids that this 61100 ticket passed, this table is a history in a ticket system.

  • A new ticket will always have the first entry in row 37 and always with history_id = 1. So when you change queue history_id will always be = 16 and the Queue id may vary, each queue has its unique id.

1 answer

1


Vyou can use multiple sub-selects for this, as I understand it, the order of the columns is given by the date column, and will always be 4 columns. So:

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

Upshot:

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 in the Sqlfiddle

Note: Insert another example ticket.

  • That’s right, that’s perfect! Just one question, you created the table with the date format as TIMESTAMP, in my table here in the database is as DATETIME and when I run the query does not work as in your example, is repeating the first record in all columns, will it be due to the format of the date field?

  • I don’t think so... if you can send the script that creates the table... I can see... I saw in your questions that several of them are open yet, don’t forget to mark as answered. If you have any questions, visit the [Tour]

  • had missed the distinct... I switched to datetime also the column and changed nothing....

  • The table structure is this: CREATE TABLE ticket_history ( id bigint(20) NOT NULL AUTO_INCREMENT, history_type_id smallint(6) NOT NULL, ticket_id bigint(20) NOT NULL, queue_id int(11) NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINE=Innodb DEFAULT CHARSET=utf8;

Browser other questions tagged mysql join

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