How to MERGE data into the target table based on certain conditions . Need to group by sequence and then have to load the data

Question

CREATE TABLE transact (
    seq_id     NUMBER(10),
    q_id       NUMBER(10),
    a_val      VARCHAR2(20),
    ref_pa_id  NUMBER(10),
    seq        NUMBER(10)
);

INSERT INTO transact VALUES(11,13,null,992,1);
INSERT INTO transact VALUES(11,13,null,637,2);
INSERT INTO transact VALUES(11,14,'Manual',null,3);
INSERT INTO transact VALUES(11,15,null,083,1);

Above is the table transact with data for which I need to load the same data into the another table transact_entry but with some conditions as mentioned below:

Target table :

CREATE TABLE transact_entry (
    seq_id   NUMBER(10),
    ref_id   NUMBER(10),
    sys_otr  VARCHAR2(20),
    int_otr  VARCHAR2(20)
);

Conditions:

1. I need to group the data as per the sequence. In the above data, we have total of 4 entries out of which we need to check seq column and find the unique. So, unique will come as 3 i.e sequence 1,2,3

2. q_id are static. So, we need to insert the record into the transact_entry table based on these q_id i.e 13,14,15

3. Lets start with seq 1 which has two q_id i.e 13 and 15. Then we need to load these records into transact_entry table. For 13, we will insert the ref_pa_id of transact table into the target table column ref_id and for 15, we will insert the a_val of transact table into the target table transact_entry column int_otr

4. Lets now check for seq 2. If q_id is 13 then we will insert the ref_pa_id of transact table into the target table transact_entry column ref_id

5. Lets now check for seq 3. If q_id is 14 then we will insert the a_val of transact table into the target table transact_entry column sys_otr

Expected output:

+--------+--------+---------+---------+
| seq_id | ref_id | sys_otr | int_otr |
+--------+--------+---------+---------+
|     11 |    992 |         | null    |
|     11 |    637 |         |         |
|     11 |        | Manual  |         |
+--------+--------+---------+---------+

Tool Used: SQL Developer(18c)

Answer 1

Looks like you want a conditional aggregation, kind of

select seq_id, 
     max(case q_id when 13 then ref_pa_id end) refid,
     max(case q_id when 15 then a_val end) int_otr,
     max(case q_id when 14 then a_val end) sys_otr 
from transact
group by seq_id, seq
order by seq_id, seq

Answer 2

declare
type t_v_seq is table of transact.seq%type INDEX BY BINARY_INTEGER;
v_seq t_v_seq;
type t_v_trans is table of transact%rowtype INDEX BY BINARY_INTEGER;
v_trans t_v_trans;
begin

select DISTINCT seq bulk collect into v_seq from transact;
if v_seq.count >0 then
for i in v_seq.first..v_seq.last loop
    if v_seq.exists(i) then
    select * bulk collect into v_trans from transact where seq=v_seq(i);
    if v_trans.count>0 then
        for r in v_trans.first..v_trans.last loop
        if v_trans.exists(r) then
            if v_trans(r).q_id = 13 then 
                insert into  transact_entry (seq_id,ref_id,sys_otr,int_otr)
                values (v_trans(r).seq_id , v_trans(r).ref_pa_id,v_trans(r).a_val,null);
                
           elsif v_trans(r).q_id = 14 then
                insert into  transact_entry (seq_id,ref_id,sys_otr,int_otr)
                values (v_trans(r).seq_id ,null,v_trans(r).a_val,null);
            
           elsif v_trans(r).q_id = 15 then
                insert into  transact_entry (seq_id,ref_id,sys_otr,int_otr)
                values (v_trans(r).seq_id ,null,null,v_trans(r).a_val);
            
            end if;   
        end if;
        end loop;
    end if;
    end if;
end loop;
end if;
    delete from transact_entry where ref_id is null and sys_otr is null and int_otr is null;
end;