SQLTeam.com | Weblogs | Forums

List agg - duplicates in one cell


#1

How do I eliminate the duplicate records in the one cell?

select distinct pat_enc.pat_id
,LISTAGG(ZC_SPECIALTY.name, ',') WITHIN GROUP (ORDER BY ZC_SPECIALTY.name) specialty
from pat_enc

id visit_type
000027424 Gastroenterology,Gastroenterology
000038355 Cardiology,Cardiology,Cardiology,Cardiology,Cardiology


#2

How about this:

select pat_id
      ,listagg([name],',') within group(order by [name]) as speciality
  from (select distinct
               pat_enc.pat_id
              ,zc_speciality.[name]
          from pat_enc
               --your join to zc_speciality
       ) as a