sql - Oracle LISTAGG DISTINCT with multiple conditions

Question

For Oracle12, I have the table shown below

ID	CONTACTCODE	CONTACTPERSON
1	X	CLIENT
1	X	CLIENT
1	X	WIFE
1	Y	CLIENT
1	Z	WIFE
1	Z	CLIENT
1	Z	HUSBAND
1	W	CLIENT
2	Y	CHILD
2	Y	WIFE
2	Y	CLIENT
3	Y	HUSBAND
3	W	CLIENT

question from:https://stackoverflow.com/questions/65921451/oracle-listagg-distinct-with-multiple-conditions

Answer 1

You can use the distinct, case..when and LISTAGG as follows:

select id, 
       coalesce(listagg(case when isclient = 'CLIENT' then contactcode end,',') 
                  within group (order by contactcode), '0') as maincode,
       coalesce(listagg(case when isclient = 'NOT CLIENT' then contactcode end,',') 
                  within group (order by contactcode), '0') as ref 
(select distinct id, contactcode, 
        case when contactperson = 'CLIENT' then 'CLIENT' else 'NOT CLIENT' end as isclient
  from your_table t
 where t.contactcode in ('X', 'Y', 'Z') ) t
 group by id