Question

SQL SERVER 2008, 如何将第一个表通过第二个表转换成第三个表，有逗号隔开了

Answer 1

可如下
create table tab1(col1 int identity,col2 varchar(8));
create table tab2(col1 varchar(10) ,col2 varchar(8));

insert into tab1 (col2)
select 'ABD'
union all
select 'AE'

insert into tab2
select 'A','啊'
union all
select 'B','吧'
union all
select 'C','才'
union all
select 'D','的'
union all
select 'E','额'
;

select T1.col1,
STUFF((select ',' + tab2.col2 from (select col1,substring(col2,number,1) as col2 from tab1 a,master..spt_values b where b.number <= len(col2) and b.type = 'P' and b.number > 0) as T2 join tab2 on T2.col2 = tab2.col1 where T1.col1 = T2.col1 order by T2.col2 for xml path('')),1,1,'')
from
(select col1,substring(col2,number,1) as col2 from tab1 a,master..spt_values b where b.number <= len(col2) and b.type = 'P' and b.number > 0) as T1
group by T1.col1;

Answer 2

select A

stuff((select '、'+t2.B from table1 t1 inner join table2 t2 on t2.A like '%'+t1.B+'%'
where t1.A=t.A for xml path('')),1,1,'') as B

from table1 t