How to generate distinct values in a column with comma separated using LISTAGG function in Oracle
with data as
(
select BNU_USER,
(
select BNU_USER,
STATE,
DISTRICT_NAME,
listagg(VILLAGE_NAME,',') within group ( order by VILLAGE_NAME) VILLAGE_NAME,
listagg(PINCODE,',') within group ( order by PINCODE) PINCODE
from sanchar_saathi
where BNU_USER<>'NA'
group
listagg(PINCODE,',') within group ( order by PINCODE) PINCODE
from sanchar_saathi
where BNU_USER<>'NA'
group
by BNU_USER, STATE,DISTRICT_NAME
)
select BNU_USER, STATE,DISTRICT_NAME,
regexp_replace(VILLAGE_NAME,'([^,]+)(,\1)+', '\1') VILLAGE_NAME,
regexp_replace(PINCODE,'([^,]+)(,\1)+', '\1') PINCODE
from data;
)
select BNU_USER, STATE,DISTRICT_NAME,
regexp_replace(VILLAGE_NAME,'([^,]+)(,\1)+', '\1') VILLAGE_NAME,
regexp_replace(PINCODE,'([^,]+)(,\1)+', '\1') PINCODE
from data;
Tags:
Oracle