with TABLE_A as (

select '1' [id],'1,2' [NAME_]

union all

select '2' [id],'3' [NAME_]

union all

select '3' [id],'4,5' [NAME_]

),

TABLE_B AS

(

select '1' [id],'张三' [NAME_]

union all

select '2' [id],'李四' [NAME_]

union all

select '3' [id],'王五' [NAME_]

union all

select '4' [id],'孙六' [NAME_]

union all

select '5' [id],'赵七' [NAME_]

)

SELECT ID,STRING_AGG(NAME2_,',') FROM (

SELECT T.*,TABLE_B.NAME_ [NAME2_] FROM (

select A.id,A.NAME_

,substring(A.NAME_,b.number,charindex(',',A.NAME_+',',b.number)-b.number) as [序列号] 

from TABLE_A a with(nolock),master..spt_values b with(nolock) 

where b.number>=1 and b.number<=len(a.NAME_) and b.type='P'

and substring(','+A.NAME_,number,1)=','

) T INNER JOIN

TABLE_B ON T.序列号=TABLE_B.id

) T2 GROUP BY ID

虽然是多了点。但是这个也算简单了。在sql server中使用替换效率不错的呀。。不然你还得单独处理分隔再建立一个字典表匹配其实也麻烦。这样看上去函数套得多了。但是不麻烦。改起来容易

在sql里面做分割合并不太好。

可以不替换，保持英文，到报表内用单元格形态去替换