字段值通译

字段值翻译
一张分类表
编码 内容
01 a
02 b
03 c
04 d
05 e

测试数据表
id 内容
1 01,05
2 02
3 01,03
4 02,05
5 01,02,03
6 01,02,04,05
7 02,04
数据结果
id 内容
1 a,e
2 b
3 a,c
4 b,e
5 a,b,c
6 a,b,d,e
7 b,d



[解决办法]

SQL code

--> 测试数据：[A1]if object_id('[A1]') is not null drop table [A1]create table [A1]([编码] varchar(2),[内容] varchar(1))insert [A1]select '01','a' union allselect '02','b' union allselect '03','c' union allselect '04','d' union allselect '05','e'--> 测试数据：[B2]if object_id('[B2]') is not null drop table [B2]create table [B2]([id] int,[内容] varchar(11))insert [B2]select 1,'01,05' union allselect 2,'02' union allselect 3,'01,03' union allselect 4,'02,05' union allselect 5,'01,02,03' union allselect 6,'01,02,04,05' union allselect 7,'02,04'gowith tas(select     b.id,    a.内容 from     [B2] binner join     [A1] aon     CHARINDEX(a.编码,b.内容)>0)select     a.id,    内容=stuff((SELECT ','+内容 from     t where     a.id=t.id for xml path('')),1,1,'')from     t  agroup by     a.id/*id    内容----------------------1    a,e2    b3    a,c4    b,e5    a,b,c6    a,b,d,e7    b,d*/