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:
x
('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,'')
Advertisement
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