sql - MySQL some rows to columns -

i have table in mysql 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) 

this table of trucks enter our terminal drop container, , pick 1 40" or 2 20" gate out (3 transactions, 3 rows). trucker drops container , goes away empty (1 transaction), there no out transaction. or may come empty pick full container (1 transaction), there no in transaction, 1 or 2 out (2 transactions), if picks 1 40 or 2 20s. there times when comes in 2 20 foot, , leaves 2 20s well, having 4 transactions. time in , time out same every placard, can take of records, no worries that.

the key time_in + placard, since same placard can multiple trips in , out on same day. timestamp same each trip.

in end, no more 4 transactions every trip, , need single row report per trip, displaying each transaction details (container, sztp, , line), , if have 1 or two, other remaining transaction details null. unfamiliar maritime terms, sztp means size/type, 40dr means 40 foot dry, 20dr 20 foot dry, 40rh 40 foot reefer high cube, , on , forth.

i need end this:

time in placard cont1     sztp1  line1 cont2     sztp2 line2 cont3     sztp3 line3 cont4     sztp4 line4 time out ======= ======= ========= =====  ===== ========= ===== ===== ========= ===== ===== ========= ===== ===== ======== 10:15   254114  clhu12345 40dh   mae   maeu45678 20dr  sea   ttnu98765 20dr  chi   null      null  null  10:54 11:23   664524  fscu13479 40rh   seb   ponu55588 40dr  mab   null      null  null  null      null  null  11:55 13:01   542234  tlhu77665 40rh   mol   null      null  null  null      null  null  null      null  null  13:23 13:36   232212  mlhu22341 20dr   cmd   null      null  null  null      null  null  null      null  null  13:49 14:03   187852  amfu56041 20dr   cmd   cclu44112 20dr  chn   null      null  null  null      null  null  14:48 

the intended use of table birt report.

thanks help.

btw, asked similar question, not clear beginning, reported moderation , posted one. apologies that. hope can me.

a birt crosstab can handle this, example this report near want do: transposing 5 order lines columns.1

the advantage of option is, dynamic: don't have hard-code anywhere fields of each line number such cont1, cont2, sztp1, sztp2 etc.

to achieve same in context need line number each transaction though. such line number computed either in mysql stored procedure, either in computed column of birt dataset, checking if "time in" , "placard" values of current row different previous row. of course query needs order transactions "time-in" + "placard" this.

once line number available in dataset can design datacube 2 groups , 2 measures

• first group: "time-in"+"placard". add sublevels display these fields in separate columns such "order" , "date" in screenshot.
• second group: line number
• measure 1: field:"container" type:string aggregatefunction: first
• measure 2: field:"sztp" type:string aggregatefunction: first