Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
   ref_id INTEGER  NOT NULL 
  ,data   INTEGER  NOT NULL
  ,ORN    INTEGER  NOT NULL
  ,flag   INTEGER  NOT NULL
);
begin
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,100,0,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,200,1,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,300,2,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,400,3,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,110,0,1);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,210,1,1);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,150,0,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,250,1,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,350,2,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (1,450,3,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,500,0,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,600,1,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,700,2,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,800,3,0);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,120,0,1);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,220,1,1);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,320,1,1);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,420,1,1);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,170,0,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,270,1,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,370,2,2);
INSERT INTO mytable(ref_id,data,ORN,flag) VALUES (2,470,3,2);
end;
/
1 rows affected

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


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