How to create a query to get the multiple row data in comma separated format in one row in Oracle

I have a requirement where I have column ‘NOTECODE’ in database table POLICY which contains multiple Note Codes in comma separated format. Ex: CC,TD,HL

POLICY :

 POLICYNO  | NOTECODE
 ----------|----------
 ABC001    | CC,TD,HL
 ----------|----------
 ABC002    | CC,TD

ALSO there is a CODEDESC table which contains the description as below

 CODE  | DESCRIPTION
 ------|----------
 CC    | Credit Card Payment
 ------|----------
 TD    | Yee
 ------|--------------
 HL    |Hospital Credit (Letter)

I want to fetch data using query which gives the POLICYNO, NOTECODE and DESCRIPTION as mentioned below:

 POLICYNO  | NOTECODE   | DESCRIPTION
 ----------|----------  |----------
 ABC001    | CC,TD,HL   | Credit Card Payment, Yee, Hospital Credit (Letter)
 ----------|----------  |----------
 ABC002    | CC,TD      | Credit Card Payment, Yee  

How can I do it?

Answer

You can use JOIN and aggregation:

select p.policyno, p.notecode,
       listagg(description, ', ') within group (order by instr(p.notecode, cd.code))
from policy p left join
     codedesc cd
     on ',' || p.notecode || ',' like '%,' || cd.code || ',%'
group by p.policyno, p.notecode;

Note that this is not efficient! This is a work-around because you are stuck with a really bad data model. Your efforts should really be to fix the data model.