MySQL span multiple rows in fields

Question

I have a table like this:

Trans Time_In Placard Container Sztp Line Time_Out   
===== ======= ======= ========= ==== ==== ========
IN    10:15   254114  CLHU12345 40DH MAE  10:54   <In transaction
OUT   10:15   254114  MAEU45678 20DR SEA  10:54   <Out Transaction (same placard)
OUT   10:15   254114  TTNU98765 20DR CHI  10:54   <Out Transaction (same placard)

IN    11:23   664524  FSCU13479 40RH SEB  11:55   <In transaction
OUT   11:23   664524  PONU55588 40DR MAB  11:55   <Out Transaction (same placard)

IN    13:01   542234  TLHU77665 40RH MOL  13:23   <In transaction (no out)

OUT   13:36   232212  MLHU22341 20DR CMD  13:49   <Out Transaction (no in)

OUT   14:03   187852  AMFU56041 20DR CMD  14:48   <Out Transaction (no in)
OUT   14:03   187852  CCLU44112 20DR CHN  14:48   <Out Transaction, same placard (no in)

Is a table of trucks that enter out terminal to drop a container, and sometimes to pick one 40" or two 20" to gate out. Sometimes a trucker simply drops a container and goes away empty, so there is no OUT transaction. Or it may come empty to pick a full container, so there is no IN transaction, but just one or two OUT, if he picks one 40 or two 20s. Time In and Time out is the same for every placard, so I can take it from any of the records, so no worries about that.

The key is Time_In + Placard, since the same placard can do multiple trips in and out. The timestamp is exactly the same for each trip.

I need to end up with something like this:

Trans Time In Placard Cont1     Sztp1  Line1 Cont2     Sztp2 Line2 Cont3     Sztp3 Line3 Time Out
===== ======= ======= ========= =====  ===== ========= ===== ===== ========= ===== ===== ========
IN    10:15   254114  CLHU12345 40DH   MAE   MAEU45678 20DR  SEA   TTNU98765 20DR  CHI   10:54
IN    11:23   664524  FSCU13479 40RH   SEB   PONU55588 40DR  MAB   null      null  null  11:55
IN    13:01   542234  TLHU77665 40RH   MOL   null      null  null  null      null  null  13:23
OUT   13:36   232212  MLHU22341 20DR   CMD   null      null  null  null      null  null  13:49
OUT   14:03   187852  AMFU56041 20DR   CMD   CCLU44112 20DR  CHN   null      null  null  14:48

Thanks for your help.

UPDATE: Just rewrote the entire question, since it was not clear enough. Also the title was wrong, is not multiple fields into rows, but the other way around: multiple rows to fields. Sorry for that.

Answer 1

I think one pass cursor operations in a MySQL store procedure or your programming language can be more efficient. Merging all those complicated relationships to one query is not easy for implementation, review or maintenance.

Here is another solution to use a temp table to build and hold the result, where t1 is your raw data table and t3 is the result table.

drop table if exists t3;

create temporary table t3 (placecard int, Trans varchar(10), TimeIn varchar(10), ContIn varchar(20), InSize varchar(10), InLine varchar(10), ContOut1 varchar(20),
   ContSize1 Varchar(10), ContLine1 varchar(10), ContOut2 varchar(20), ContSize2 varchar(10), ContLine2 Varchar(10), TimeOut varchar(10) );


insert into t3
(placecard, timein, timeout)
select placecard, min(timein), max(timeout) from t1
group by placecard;


update t3
join t1 
on t3.placecard = t1.placecard  and t3.timein = t1.timein and t1.transaction='IN'
set trans ='IN', contin = t1.Container, InSize = t1.Size, InLine = t1.Line
where t3.placecard>0;

update t3
join t1
on t3.placecard= t1.placecard and t1.transaction='OUT'
set contOut1 = t1.Container , ContLine1 = t1.line , ContSize1 = t1.size
where t3.placecard>0;

update t3
join t1
on t3.placecard= t1.placecard and t1.transaction='OUT' and t3.contOut1 <> t1.Container
set contOut2 = t1.Container , ContLine2 = t1.line , ContSize2 = t1.size
where t3.placecard>0 ;


select * from t3;

You can build it into a store procedure and add parameters to limit the scope.

Or you can build it into a scheduled job which updates the schedule table.