Concatenating selective string values using STUFF

I’m trying to concatenate values onto one line, but only if the EEID is the same. I know how to concatenate values using Stuff, but I can’t seem to get it to work with only selective records.
For the table below I only want to concatenate eeid = 690.

My result should be:

   ('10218', '12321')

   declare @t table(companyid int, eeid int, claimid int)
   insert into @t
   values(11, 690, 10218),
   (11, 690, 12321),
   (11, 707, 21822),
   (11, 321, 21321)

   select * from @t

   select  stuff((select ',' + cast(claimid as varchar(max)) 
                from @t
                for xml path('')),1,1,'')

Answer

You need a correlated clause:

select e.eeid,
       stuff((select ',' + cast(claimid as varchar(max)) 
              from @t t
              where t.eeid = e.eeid
              for xml path('')
             ), 1, 1, ''
            )
from (select distinct eeid from @t) e

Advertisement

Answer