27,579
社区成员
发帖
与我相关
我的任务
分享
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)
select sname,
keylist=stuff((select ','+keyname from keywords_tb
where charindex(','+rtrim(id)+',',','+a.keylist+',')>0 for xml path('')),1,1,'')
from tblist a