select sname,
   keylist=stuff((select ','+keyname from keywords_tb 
   where charindex(','+rtrim(id)+',',','+a.keylist+',')>0 for xml path('')),1,1,'')
from tblist a

------解决方案--------------------
你的表结构没什么好的效率

------解决方案--------------------
try this,
SQL code

create table tblist
(id int, sname varchar(5), keylist varchar(20))

insert into tblist
select 1, 'ABC', '1,2,3,4' union all
select 2, 'BCD', '3,4,5' union all
select 3, 'EEE', '6,7,8,9,10,11,12,13' union all
select 4, 'FFF', '1,5,7' union all
select 5, 'XXX', '6,8,9' union all
select 6, 'PPP', '7,9,10,11,12,15'

create table keywords_tb(id int, keyname varchar(5))

insert into keywords_tb
select 1, 'k1' union all
select 2, 'k2' union all
select 3, 'k3' union all
select 4, 'k4' union all
select 5, 'k5' union all
select 6, 'k6' union all
select 7, 'k7' union all
select 8, 'k8' union all
select 9, 'k9' union all
select 10, 'k10' union all
select 11, 'k11' union all
select 12, 'k12' union all
select 13, 'k13' union all
select 14, 'k14' union all
select 15, 'k15' union all
select 16, 'k16'


with t as
(select c.id,d.keyname from
(select a.id,
substring(a.keylist,b.number,charindex(',',a.keylist+',',b.number)-b.number) ak
from tblist a
inner join master.dbo.spt_values b
on b.[type]='P' and 
substring(','+a.keylist,b.number,1)=',') c
inner join keywords_tb d on c.ak=d.id),
t3 as
(select t1.id,
cast((select t2.keyname+',' from t t2 where t2.id=t1.id for xml path('')) as varchar) ak2
from t t1
group by t1.id
)
select a.id 'col0',b.sname 'col1',
left(a.ak2,len(a.ak2)-1) 'col2'
from t3 a
inner join tblist b
on a.id=b.id
order by a.id

col0        col1  col2
----------- ----- ------------------------------
1           ABC   k1,k2,k3,k4
2           BCD   k3,k4,k5
3           EEE   k6,k7,k8,k9,k10,k11,k12,k13
4           FFF   k1,k5,k7
5           XXX   k6,k8,k9
6           PPP   k7,k9,k10,k11,k12,k15

(6 row(s) affected)