原始表结构:(D为制单)
DpNum Re FunId zhidan Grade Person
-----------------------------------------
XZ 制单 001 D 0 A,B
XZ 审核 001 D 1 A,B
XZ 审核 001 D 2 A,B
XZ 批准 001 D 3 A,B
转换成下表结构:
DpNum Re FunId Grade Person
---------------------------------
XZ 制单 001 0 D
XZ 审核 001 1 A
XZ 审核 001 2 B
XZ 批准 001 3 no
DpNum Re FunId zhidan Grade Person
-----------------------------------------
XZ 制单 001 D 0 A,B
XZ 审核 001 D 1 A,B
XZ 审核 001 D 2 A,B
XZ 批准 001 D 3 A,B
转换成下表结构:
DpNum Re FunId Grade Person
---------------------------------
XZ 制单 001 0 D
XZ 审核 001 1 A
XZ 审核 001 2 B
XZ 批准 001 3 no
Grade代表几级审核;0代表制单;1代表1级审核;2代表2级审核;3为3级审核;后面依次...
;with t(DpNum,Re,FunId,zhidan,Grade,Person) as(
select 'XZ',N'制单','001','D',0,'A,B' union all
select 'XZ',N'审核','001','D',1,'A,B' union all
select 'XZ',N'审核','001','D',2,'A,B' union all
select 'XZ',N'批准','001','D',3,'A,B'
)
select DpNum,Re,FunId,Grade,
(case Grade when 0 then zhidan
else cast(cast('<p>'+REPLACE(Person,',','</p><p>')+'</p>'
as xml).query('data(/p[sql:column("Grade")])') as varchar(10))
end) Person
from t;
/*
XZ 制单 001 0 D
XZ 审核 001 1 A
XZ 审核 001 2 B
XZ 批准 001 3
*/