mssql中将查询结果用逗号拼接

业务部门有个需求，付款申请中，采购订单与采购申请是1：N关系，且是多个采购订单一起付款，业务部门要求采购订单付款的时候，需要关联到对应的采购申请流程。

• 此处不讨论需求的合理性

问题不大，sql查询的时候将采购申请流程结果用逗号拼接即可，且字段赋予多流程即可。

但麻烦点就是这个sql写的麻烦，此文仅用于记录这个麻烦sql语句聊以备用。

CREATE view vm_mrg_purchase_order_pool_05 as 
with a as 
(select a.id,a.cgddh,b.dycgsqlc from uf_mrg_purchase_order_pool as a left join uf_mrg_purchase_order_pool_dt1 as b on a.id=b.mainid)
SELECT id,cgddh, STUFF((
    SELECT DISTINCT ',' + CAST(dycgsqlc AS VARCHAR)
    FROM a t2
    WHERE t2.id = t1.id
    FOR XML PATH('')), 1, 1, '') AS Result
FROM a t1
GROUP BY id,cgddh

SELECT STUFF((
    SELECT DISTINCT  ',' + CAST(requestid AS VARCHAR)
    FROM formtable_main_1007
    WHERE cpzy = 377
    FOR XML PATH('')), 1, 1, '') AS Result