I have a table in MySQL 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)
This is a table of trucks that enter our terminal to drop a container, and sometimes to pick one 40" or two 20" to gate out (3 transactions, thus 3 rows). Sometimes a trucker simply drops a container and goes away empty (1 transaction), so there is no OUT transaction. Or it may come empty to pick a full container (1 transaction), so there is no IN transaction, but just one or two OUT (2 transactions), if he picks one 40 or two 20s. There are even times when comes in with two 20 foot, and leaves with 2 20s as well, having 4 transactions. The 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 on the same day. The timestamp is exactly the same for each trip.
In the end, it will be no more than 4 transactions for every trip, and we need a single row report per trip, displaying each transaction details (container, sztp, and line), and if it only have one or two, the other remaining transaction details will be null. For those unfamiliar with maritime terms, sztp means size/type, like 40DR means 40 foot dry, 20DR 20 foot dry, 40RH 40 foot reefer high cube, and so on and so forth.
I need to end up with something like 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 this table will be for a BIRT report.
Thanks for your help.
BTW, I already asked a similar question, but It was not very clear from the beginning, so I reported it to moderation and posted this one. Apologies for that. Hope you can help me.
You can create a single row of data with each unique data piece as column using sub queries.
You will need to join more sub queries to create the other columns, but this is the basic idea.