declare @a table(id varchar(50),name nvarchar(50))
insert into @a
select '1','中1' union
select '2','華1' union
select '3','人1'
declare @b table(id_list varchar(50),field2 varchar(50),field3 varchar(30))
insert into @b
select '1,2','xxx','bbbb' union
select '2','ccc','ddd' union
select '2,3','dfdf','dfdf'
select * into #t from @b b,@a a where charindex(a.id,b.id_list)>0
declare @name nvarchar(500),@id_list varchar(50)
select @id_list='',@name=''
update #t set @name=case when @id_list=id_list then @name+','+name else name end,name=@name,@id_list=id_list
select max(name) name,max(field2) field2,max(field3) field3 from #t group by id_list
drop table #t