How to generate distinct values in a column with comma separated using LISTAGG function in Oracle

 

How to generate  distinct values in a column with comma separated using LISTAGG function  in Oracle

with data as
  (
   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   
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;





Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post