oracle table grouping based on order

Question

I have an oracle table , where ref_id is the flag field is the type of data and ORN is the order of data in each ref_id :

ref_id   data    ORN   flag
  1       100     0     0
  1       200     1     0
  1       300     2     0
  1       400     3     0
  1       110     0     1
  1       210     1     1
  1       150     0     2
  1       250     1     2
  1       350     2     2
  1       450     3     2
  2       500     0     0
  2       600     1     0
  2       700     2     0
  2       800     3     0
  2       120     0     1
  2       220     1     1
  2       320     1     1
  2       420     1     1
  2       170     0     2
  2       270     1     2
  2       370     2     2
  2       470     3     2

I need to group the data as following:

• last data in flag 0 with first data in flag 1
• each data in flag 1 with the next data (number of data in flag 1 is not fix)
• last data in flag 1 with first data in flag 2

so the new table will be something like this:

ref_id    data_1    data_2
  1        400       110
  1        110       210
  1        210       150
  2        800       120
  2        120       220
  2        220       320
  2        320       420
  2        420       170

any hint how to accomplish this without using loops?

Answer 1

You can use window functions:

select ref_id, data data_1, lead_data data2
from (
    select
        t.*,
        lead(flag) over(partition by ref_id order by flag, orn) lead_flag,
        lead(data) over(partition by ref_id order by flag, orn) lead_data
    from mytable t
) t
where 
    flag = 0 and lead_flag = 1
    or (flag = 1 and lead_flag = 1)
    or (flag = 1 and lead_flag = 2)
order by ref_id, flag, orn

Demo on DB Fiddle:

REF_ID | DATA_1 | DATA2
-----: | -----: | ----:
     1 |    400 |   110
     1 |    110 |   210
     1 |    210 |   150
     2 |    800 |   120
     2 |    120 |   220
     2 |    220 |   320
     2 |    320 |   420
     2 |    420 |   170

Note that for this dataset, the where clause can be simplified as:

where 1 in (flag, lead_flag)