sql server 某个字段下的多个值拼接如何翻译

Question

单个值我可以用 case when then 翻译，他这种多种值我翻译我如何实现。

Answer 1

sqlserver 如下

with t1 as (
    select '张三' name,'A' tp
    union all select '李四','A,B'
    union all select 'xx','A,B,D,F'
    union all select 'yy','H,I,J,K'
),t2 as (
    select 'A' tp,'一' trans
    union all select 'b','二'
    union all select 'c','三'
    union all select 'd','四'
    union all select 'e','五'
    union all select 'f','六'
    union all select 'g','七'
    union all select 'h','八'
    union all select 'i','九'
    union all select 'j','十'
    union all select 'k','十一'
)
select * from t1 a
cross apply (
    select stuff((
     select '，'+trans 
     from t2 
     where charindex(tp,a.tp)>0
     order by charindex(tp,a.tp)
     for xml path('')
  ),1,1,'') ncol
) b