CODEWITHSUNDEEP
sqloracle-database
Preethi
Preethi

Reputation: 41

Can I use transpose functionality in oracle to achieve this?

I have written a view which gives me the following output

Vessel Name ETA
XYZ    1-Jul-12
XYZ    2-Jul-12
XYZ    3-Jul-12
XYZ    4-Jul-12
XYZ    5-Jul-12
XYZ    6-Jul-12
XYZ    7-Jul-12

Actually I would like to get an output like the below 

Vessel Name    ETA_1    ETA_2        ETA_3
XYZ           1-Jul-12  2-Jul-12    3-Jul-12
XYZ           2-Jul-12  3-Jul-12    4-Jul-12
XYZ           3-Jul-12  4-Jul-12    5-Jul-12
XYZ           4-Jul-12  5-Jul-12    6-Jul-12
XYZ           5-Jul-12  6-Jul-12    7-Jul-12

There could be any no of vessels available. And there could be any no of ETAs for that vessel not just 5 rows.

Why Three etas? A vessel sails from Port A on 01-jul then this would be ETA_FROM date and to Port B on 02-Jul then this would be ETA_VIA and to Port C on 03-Jul then this would be ETA_TO. Then again it sails from Port C to D on 04-Jul-2012 on 05-Jul then In this case, the ETA_FROM would be 02-Jul and ETA_VIA would be 03-Jul and ETA_TO should be 4-Jul. The least of the three eta would be the ETA_FROM date and next higher would be ETA_VIA date and next higher would ETA_TO date.

EDIT: I tried this, but the performance is poor:

select t1.vessel_name,
       t1.eta ETA_FROM ,
       t2.eta ETA_VIA , 
       t3.eta ETA_TO 
from 
  test t1,
  test t2, 
  test t3 
where t1.eta<t2.eta and 
      t3.eta>t2.eta and 
      t1.eta=(select max(test.eta) 
              from test 
              where test.eta<t2.eta) 
      and t3.eta= (select min(test.eta) 
                   from test 
                   where test.eta>t2.eta)

Upvotes: 3

Views: 70

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

select 
    * 
from (
    select 
        vessel, 
        eta, 
        lead(eta) over (order by eta) as eta2,
        lead(port) over (order by eta) as port2,
        lead(eta,2) over (order by eta) as eta3,
        lead(port,2) over (order by eta) as port3
    from your_view
)
where 
     eta3 is not null;

Upvotes: 3

Related Questions