Selecting pairs of rows in one row SQL

Question

So I have this event audit table

EventID | EventType | TaskID |   Date             | Iteration
--------------------------------------------------------------
   1    |  start    |   12   |  01/01/2016 09:00  |    1     
--------------------------------------------------------------
   2    |  ended    |   12   |  01/01/2016 09:05  |    1     
--------------------------------------------------------------
   3    |  start    |   14   |  01/01/2016 09:10  |    1     
--------------------------------------------------------------
   4    |  ended    |   14   |  01/01/2016 09:15  |    1
--------------------------------------------------------------
   5    |  start    |   12   |  01/01/2016 09:20  |    2
--------------------------------------------------------------
   6    |  ended    |   12   |  01/01/2016 09:20  |    2
--------------------------------------------------------------
   7    |  ended    |   98   |  01/01/2016 07:14  |    12
--------------------------------------------------------------
   8    |  start    |   66   |  01/01/2016 09:27  |    1

Mostly there's pairs of started/ended events of tasks with different iterations. But sometime there's only started or only ended rows.

What I want To get:

| TaskID |   Date Started     |   Date ended        |     Iteration 
----------------------------------------------------------------------
|   12   |  01/01/2016 09:00  |    01/01/2016 09:05 |          1     
----------------------------------------------------------------------
|   14   |  01/01/2016 09:10  |    01/01/2016 09:15 |          1
----------------------------------------------------------------------
|   12   |  01/01/2016 09:20  |    01/01/2016 09:20 |          2
----------------------------------------------------------------------
|   98   |         -          |    01/01/2016 07:14 |          12
----------------------------------------------------------------------
|   66   |  01/01/2016 09:27  |        -            |          1

How can I achieve that?

Oracle 11g

mortb · Answer 1 · 2016-06-30T10:18:37.193

2

I think this would work using self join:

SELECT tBase.EventId,
       tStarted.Date as DateStarted,
       tEnded.Date as DateEnded,
       tBase.Iteration
FROM  <eventAuditTable> tBase
LEFT JOIN <eventAuditTable> tStarted ON tStarted.eventType = 'Started'
       and tStarted.TaskId = tBase.TaskId
       and tStarted.Iteration = tBase.Iteration
LEFT JOIN <eventAuditTable> tEnded ON tEnded.eventType = 'Ended'
       and tBase.TaskId = tEnded.TaskId
       and tBase.Iteration = tEnded.Iteration

change the name <eventAuditTable> to the real name and try it!

edited Jun 30 '16 at 10:18

answered Jun 30 '16 at 09:50

mortb

9,361
3
26
44

This will work only if there's started events, but what if there's only "ended" events? – ifuwannaride Jun 30 '16 at 10:07
re wrote with two left outer joins instead, it should handle both missing started and ended events – mortb Jun 30 '16 at 10:19
I see, solution with 2 left join is acceptable but I like Evengy K. solution better. Nevertheless, thank you. – ifuwannaride Jun 30 '16 at 10:45

Evgeniy K. · Accepted Answer · 2016-06-30T10:11:36.043

Try this: Test data

   with t(EventID,  
     EventType,
        TaskID,
        Dates,
        Iteration) as
         (select 1,
                 'start',
                 12,
                 to_date('01/01/2016 09:00', 'mm/dd/yyyy hh24:mi'),
                 1
            from dual
          union all
          select 2,
                 'ended',
                 12,
                 to_date('01/01/2016 09:05', 'mm/dd/yyyy hh24:mi'),
                 1
            from dual
          union all
          select 3,
                 'start',
                 14,
                 to_date('01/01/2016 09:10', 'mm/dd/yyyy hh24:mi'),
                 1
            from dual
          union all
          select 4,
                 'ended',
                 14,
                 to_date('01/01/2016 09:15', 'mm/dd/yyyy hh24:mi'),
                 1
            from dual
          union all
          select 5,
                 'start',
                 12,
                 to_date('01/01/2016 09:20', 'mm/dd/yyyy hh24:mi'),
                 2
            from dual
          union all
          select 6,
                 'ended',
                 12,
                 to_date('01/01/2016 09:20', 'mm/dd/yyyy hh24:mi'),
                 2
            from dual
          union all
          select 7,
                 'ended',
                 98,
                 to_date('01/01/2016 07:14', 'mm/dd/yyyy hh24:mi'),
                 12
            from dual
          union all
          select 8,
                 'start',
                 66,
                 to_date('01/01/2016 09:27', 'mm/dd/yyyy hh24:mi'),
                 1
            from dual)

Query

    select TaskID,
           min(case EventType
                 when 'start' then
                  dates
               end),
           max(case EventType
                 when 'ended' then
                  dates
               end),
           Iteration
      from t
     group by TaskID, Iteration

If you want '-' symbol then try

 nvl(to_char(min(case EventType
             when 'start' then
              dates
           end),'mm/dd/yyyy hh24:mi'),'       -')

score 0 · Answer 3 · answered Jun 30 '16 at 10:05

Also you can use GROUP BY query to get the same result:

select TaskId,
       MIN(CASE WHEN EventType = 'start' THEN Date END) as DateStarted,
       MAX(CASE WHEN EventType = 'ended' THEN Date END) as DateEnded,
       MAX(Iteration) as Iteration

from TEvent 
GROUP BY TaskID
ORDER BY TaskID

score 0 · Answer 4 · answered Jun 30 '16 at 10:15

Try this, just a simple full outer join should get this. You can choose the iteration what ever you want either start or end or a sum of both.

select a.TaskID ,a.Date_Started,b.Date_ended,a.Iteration ,b.Iteration    
from event_audit a full outer join event_audit b 
on a.TaskID=b.TaskID;

Selecting pairs of rows in one row SQL

4 Answers4