-- Oracle 19c: LISTAGG function 
-- In previous Oracle versions, if there were duplicate values, it had to be filtered 
-- to remove duplicates

-- Oracle 19c:
-- LISTAGG ( [ALL] [DISTINCT] <measure_column> [,] 
--   [ON OVERFLOW TRUNCATE [truncate_literal] | ON OVERFLOW ERROR  [WITH | WITHOUT COUNT]]) 
--   WITHIN GROUP (ORDER BY )

WITH q_tab AS
(SELECT idx 1, RPAD('A1',5,'O') ref_cd FROM DUAL
 UNION ALL
 SELECT idx 1, RPAD('A1',5,'O') ref_cd FROM DUAL
 UNION ALL
 SELECT idx 1, RPAD('B1',5,'O') ref_cd FROM DUAL
)
SELECT  idx,
 LISTAGG(ref_cd,'|') WITHIN GROUP (ORDER BY idx) code_list
FROM q_tab
GROUP BY
 idx
;

-- The output is as shown below with duplicate codes (A1OOO)

       IDX CODE_LIST
---------- ------------------------------------------------
         1 A1OOO|A1OOO|B1OOO

WITH q_tab AS
(SELECT idx 1, RPAD('A1',5,'O') ref_cd FROM DUAL
 UNION ALL
 SELECT idx 1, RPAD('A1',5,'O') ref_cd FROM DUAL
 UNION ALL
 SELECT idx 1, RPAD('B1',5,'O') ref_cd FROM DUAL
)
SELECT idx,
 LISTAGG(DISTINCT ref_cd,'|') WITHIN GROUP (ORDER BY idx) code_list
FROM q_tab
GROUP BY
 idx
;

-- The output is as shown below without duplicate codes by using DISTINCT 

       IDX CODE_LIST
---------- ------------------------------------------------
         1 A1OOO|B1OOO