CODEWITHSUNDEEP
sqloracle-database
Edu
Edu

Reputation: 91

how to pair two rows based on the event type and time stamp in oracle

I have a scenario, where I need to pair two rows based on its property number(VAL1) and the Event type and event date. Please see the below snapshots of data that is coming from the source and the second snapshot is what expected. Can you please help me , if there is a way in oracle to achieve this?

Thank you in Advance!

Source:

This is the data from the Source

Target: (Expected)

This is the expected Output

Upvotes: 0

Views: 108

Answers (2)

GMB
GMB

Reputation: 222622

One option uses window functions, then aggregation:

select 
    listagg(id)   within group(order by eventdate) ids,
    listagg(type) within group(order by eventdate) types,
    min(eventdate) eventstart,
    max(eventdate) eventend,
    val1,
    val2
from (
    select t.*, row_number() over(partition by val1, val2 order by eventdate) rn
    from mytable t
) t
group by floor((rn - 1)/2), val1, val2

This generates pairs of rows that have the same (val1, val2), ordered by ascending eventdate: every two rows are grouped together.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143013

Looks like a little bit of aggregation. Sample data from line #1 to 7; query that actually does the job begins at line #8.

SQL> with test (id, type, event_date, val1, val2) as
  2    (select '1-1001', 'type-1', date '2020-10-14', 1234, 'abc' from dual union all
  3     select '1-1002', 'type-2', date '2020-10-15', 1234, 'abc' from dual union all
  4     --
  5     select '1-1025', 'type-1', date '2020-09-01', 1123, 'abc' from dual union all
  6     select '1-1026', 'type-2', date '2020-09-02', 1123, 'abc' from dual
  7    )
  8  select
  9    listagg(id, ', ') within group (order by event_date) ids,
 10    listagg(type, ', ') within group (order by event_date) types,
 11    min(event_date) event_start_dt,
 12    max(event_date) event_end_dt,
 13    val1,
 14    val2
 15  from test
 16  group by val1, val2
 17  order by 1;

IDS             TYPES           EVENT_STAR EVENT_END_       VAL1 VAL
--------------- --------------- ---------- ---------- ---------- ---
1-1001, 1-1002  type-1, type-2  2020-10-14 2020-10-15       1234 abc
1-1025, 1-1026  type-1, type-2  2020-09-01 2020-09-02       1123 abc

SQL>

Upvotes: 0

Related Questions