How to merge collection data in PLSQL

Question

I wan to merge the collection data that I got separately

Example

CREATE TYPE VARCHAR_NTT AS TABLE OF VARCHAR2(50 CHAR);   

select * FROM( (with test_data1 as 
(
select '1' as ID, 'AAA' as NAME, VARCHAR_NTT('AAA') as SUB from dual
union all  select '2', 'BBB', VARCHAR_NTT('AAA','BBB') from dual
union all  select '3', 'CCC', VARCHAR_NTT('AAA','BBB','CCC') from dual
),
test_data2 as 
(
select '4' as ID, 'DDD' as NAME, VARCHAR_NTT('AAA') as SUB from dual
union all  select '5', 'EEE', VARCHAR_NTT('DDD','EEE') from dual
union all  select '1', 'AAA', VARCHAR_NTT('BBB','CCCC','DDD') from dual
union all  select '2', 'BBB', VARCHAR_NTT('FFF','GGG') from dual
)
SELECT  ID,NAME,SUB FROM test_data1
union all SELECT  ID,NAME,SUB FROM test_data2) a);

I have some data like how I mentioned above, And I need to Merge that result like below

1   AAA APPDATA.VARCHAR_COLL('AAA','BBB','CCC','DDD')
2   BBB APPDATA.VARCHAR_COLL('AAA','BBB','FFF','GGG')
3   CCC APPDATA.VARCHAR_COLL('AAA','BBB','CCC')
4   DDD APPDATA.VARCHAR_COLL('AAA')
5   EEE APPDATA.VARCHAR_COLL('DDD','EEE')

Basically I need to merge SUB based on ID and NAME

Answer 1

You can use a FULL OUTER JOIN and MULTISET UNION DISTINCT:

WITH test_data1 (id, name, sub) as (
  select '1', 'AAA', VARCHAR_NTT('AAA') from dual union all
  select '2', 'BBB', VARCHAR_NTT('AAA','BBB') from dual union all
  select '3', 'CCC', VARCHAR_NTT('AAA','BBB','CCC') from dual
),
test_data2 (id, name, sub) as (
  select '4', 'DDD', VARCHAR_NTT('AAA') from dual union all
  select '5', 'EEE', VARCHAR_NTT('DDD','EEE') from dual union all
  select '1', 'AAA', VARCHAR_NTT('BBB','CCCC','DDD') from dual union all
  select '2', 'BBB', VARCHAR_NTT('FFF','GGG') from dual
)
SELECT  COALESCE(t1.id, t2.id) AS id,
        COALESCE(t1.name, t2.name) AS name,
        COALESCE(t1.sub, VARCHAR_NTT()) MULTISET UNION DISTINCT COALESCE(t2.sub, VARCHAR_NTT()) AS sub
FROM    test_data1 t1
        FULL OUTER JOIN test_data2 t2
        ON t1.id = t2.id AND t1.name = t2.name
ORDER BY id;

Which outputs:

ID	NAME	SUB
1	AAA	AAA,BBB,CCCC,DDD
2	BBB	AAA,BBB,FFF,GGG
3	CCC	AAA,BBB,CCC
4	DDD	AAA
5	EEE	DDD,EEE

fiddle