Sql Server stuff使用
----原始数据----
select ActivityName+':'+CONVERT(varchar(2), COUNT(*)) AS 退件次数 from FAERejectData WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120' GROUP BY ActivityName
查询结果如下
EQA会签:1
分析评估:2
责任工程师确认:1
---期望数据----- select STUFF((SELECT ';'+tmp.退件次数 FROM ( select ActivityName+':'+CONVERT(varchar(2), COUNT(*)) AS 退件次数 from FAERejectData WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120' GROUP BY ActivityName)tmp where 1= 1 FOR XML PATH('')),1,1,'')
查询结果:EQA会签:1;分析评估:2;责任工程师确认:1
二、
---原始数据--- select ActivityName,r.RejectReason from FAERejectData R WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120'
查询结果如下:
EQA会签 样品NG
分析评估 资料NG
责任工程师确认 样品NG
分析评估 NVT内部原因
------期望数据------------------- select ActivityName, items=stuff((select ','+RejectReason from (select distinct ActivityName,RejectReason from
FAERejectData WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120') t where ActivityName=s.ActivityName for xml path('')), 1, 1, '') from (select distinct ActivityName,RejectReason from FAERejectData WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120') s group by ActivityName
查询数据如下,3行2列:
EQA会签 样品NG
分析评估 NVT内部原因,资料NG
责任工程师确认 样品NG
----再次合并数据
select STUFF((SELECT ','+tmp.退件原因 FROM (
select ActivityName+':'+stuff((select ';'+RejectReason from (select distinct ActivityName,RejectReason from FAERejectData WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120') t
where ActivityName=s.ActivityName for xml path('')), 1, 1, '') as 退件原因
from (select distinct ActivityName,RejectReason from FAERejectData WHERE FInstanceID = 'f9c2cc85-c05f-4545-8095-97861d0e2120') s
group by ActivityName)tmp where 1= 1 FOR XML PATH('')),1,1,'')
查询结果:单行单列
EQA会签:样品NG,分析评估:NVT内部原因;资料NG,责任工程师确认:样品NG