table 1 : id code1 0012 001,0023 001,002,003table 2:code name001 数学002 体育003 美术要求结果 id name1 数学2 数学，体育3 数学，体育，美术
--测试数据
with table1(id,code)
as (
select 1,
'001' union all
select 2,
'001,002' union all
select 3,
'001,002,003'),
table2(code,name) as(
select '001',
'数学' union all
select '002',
'体育' union all
select '003',
'美术')
--用charindex和for xml path实现批量替换的功能,适用于sql server 2005及以上版本
select table1.id,
stuff((
select ','+table2.name
from table2
where charindex(
','+table2.code
+',',
','+table1.code
+',')
>0
order by table2.code
for xml path(
'')
),1,
1,
'')
as name
from table1
转载于:https://www.cnblogs.com/Tinoloving/p/4633187.html
相关资源：sql取逗号分隔函数
