Self joining table with a condition

Question

I have a table of the following type:

Table dummy1:

e_n  t_s  item
a     t1   c
a     t2   c
a     t3   c
a     t4   c
b     p1   c
b     p2   c
b     p3   c 
b     p4   c

t1, t2, t3, t4, p1, p2, p3, p4 are timestamps in ascending order. t1, t2, t3, t4 are timestamps in ascending order for event_name 'a'. p1, p2, p3, p4 are timestamps in ascending order for event_name 'b'.

c is the item_number for which these events 'a' and 'b' are occurring.

I am trying to write a query for which the result should be as follows:

e_n1 e_n2  item  t_s_1 t_s_2
a     b     c     t1    p1
a     b     c     t2    p2 
a     b     c     t3    p3
a     b     c     t4    p4

I have tried the following code:

select l.e_n as e_n_1, m.e_n as e_n_2, l.item, l.t_s as t_s_a, 
m.t_s as t_s_b from (
(select * from  dummy where e_n = 'a') l 
join 
(select * from  dummy where e_n = 'b') m 
on l.item = m.item and l.t_s < m.t_s

The join l.item = m.item is needed as there are many other items c1, c2, c3 with the same structure

The result is:

   e_n1 e_n2  item  t_s_a t_s_b
    a     b     c     t1    p1
    a     b     c     t1    p2
    a     b     c     t1    p3
    a     b     c     t1    p4
    a     b     c     t2    p1 
    a     b     c     t2    p2
    a     b     c     t2    p3

so on

How can I achieve my result in an efficient way?

Answer 1

select      min (case when e_n = 'a' then 'a' end)  as e_n1
           ,min (case when e_n = 'b' then 'b' end)  as e_n2
           ,item
           ,min (case when e_n = 'a' then t_s end)  as t_s_1
           ,min (case when e_n = 'b' then t_s end)  as t_s_2

from       (select      d.*
                       ,row_number () over (partition by item,e_n order by t_s) as rn

            from        dummy as d
            ) d

group by    item
           ,rn

---

+------+------+------+-------+-------+
| e_n1 | e_n2 | item | t_s_1 | t_s_2 |
+------+------+------+-------+-------+
| a    | b    | c    | t1    | p1    |
| a    | b    | c    | t2    | p2    |
| a    | b    | c    | t3    | p3    |
| a    | b    | c    | t4    | p4    |
+------+------+------+-------+-------+