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