if not object_id('t1') is null
    drop table t1
Go
Create table t1([CDS] nvarchar(5))
Insert t1
select N'1,2,3' union all
select N'1,3'
Go
if not object_id('t2') is null
    drop table t2
Go
Create table t2([CD] int,[Name] nvarchar(1))
Insert t2
select 1,N'孙' union all
select 2,N'李' union all
select 3,N'王'
Go
select [CDS],
        stuff((select ','+[Name]
        from t2 
        where charindex(','+ltrim([CD] )+',',','+t.[CDS] +',')>0
        for xml path('')),1,1,'')Name
from t1 t