SQL Oracle - Find all combination of events possible based on date

Question

I have a table with the following information:

Data Sample

**Table 1**  
palletNumber-- event-- --recordDate
-----1-----------A-------01/11/2015 01:00
-----1-----------B-------01/11/2015 02:00
-----1-----------C-------01/11/2015 03:00
-----1-----------D-------01/11/2015 04:00
-----2-----------A-------01/11/2015 01:10
-----2-----------C-------01/11/2015 01:15
-----2-----------E-------01/11/2015 01:20

I want to select all the possible combinations of events that appear in the table in the sequence of the recordDate by palletNumber. I tried various statements with Row Number, Over Partition but this did not get me close to what I am looking for... Any direction on where to go?

This would be the output table for example:

**Table 2**  
event1-- event2--
---A------B------
---B------C------
---C------D------
---A------C------
---C------E------

Thanks,

try `lead/lag (event) over (partition by palletNumber order by recordDate)` — Tatiana, Nov 04 '15 at 11:35

score 2 · Accepted Answer · answered Nov 04 '15 at 11:43

You can get the previous or next event using lag() or lead():

select event,
       lead(event) over (partition by palletnumber order by recorddate) as next_event
from datasample;

If you want to eliminate duplicates, I would be inclined to use group by, because this also gives the ability to count the number of times that each pair appears:

select event, next_event, count(*) as cnt
from (select event,
             lead(event) over (partition by palletnumber order by recorddate) as next_event
      from datasample
     ) ds
group by event, next_event;

Thanks a lot, I did not know about lead/lag! Learn more every day. — Aurax22, Nov 04 '15 at 12:04

Olafur Tryggvason · Answer 2 · 2015-11-04T11:54:47.277

Use Case:

select case when palletNumber = 1 then event else null end as event1,
       case when palletNumber = 2 then event else null end as event2,
       recordDate
  from table1

Then you can work with the data using lead/lag or sum() / group by to get it in one row.

Assuming events 1/2 only have one record per date

  select recordDate, max (event1), max (event2)
    from (  select case when palletNumber = 1 then event else null end as event1,
                   case when palletNumber = 2 then event else null end as event2,
                   recordDate
              from table1
          order by recordDate) tab2
group by recordDate

It would work if I had a limited number of palletNumber but I have hundreds. I did use the lead/lag and group by however! Thanks — Aurax22, Nov 04 '15 at 13:25

SQL Oracle - Find all combination of events possible based on date

2 Answers2