--> 测试数据：[A1]
if object_id('[A1]') is not null 
drop table [A1]
create table [A1](
[编码] varchar(2),
[内容] varchar(1)
)
insert [A1]
select '01','a' union all
select '02','b' union all
select '03','c' union all
select '04','d' union all
select '05','e'
--> 测试数据：[B2]
if object_id('[B2]') is not null 
drop table [B2]
create table [B2](
[id] int,
[内容] varchar(11)
)
insert [B2]
select 1,'01,05' union all
select 2,'02' union all
select 3,'01,03' union all
select 4,'02,05' union all
select 5,'01,02,03' union all
select 6,'01,02,04,05' union all
select 7,'02,04'
go

with t
as(
select 
    b.id,
    a.内容 
from 
    [B2] b
inner join 
    [A1] a
on 
    CHARINDEX(a.编码,b.内容)>0
)
select 
    a.id,
    内容=stuff((SELECT ','+内容 
from 
    t 
where 
    a.id=t.id for xml path('')),1,1,'')
from 
    t  a
group by 
    a.id
/*
id    内容
----------------------
1    a,e
2    b
3    a,c
4    b,e
5    a,b,c
6    a,b,d,e
7    b,d
*/