David Burrows (12/15/2014)
WITH vv (VCHVendor,VGLVoucher)
AS (SELECT DISTINCT VCHVendor,VGLVoucher
FROM VoucherGLDist vgl
JOIN Voucher v ON v.VCHVoucherNbr = vgl.VGLVoucher
WHERE vgl.VGLJEBatch = 21174)
SELECT vv.VCHVendor, SUM(vgl.VGLAmount) AS [VGLAmount],
STUFF((SELECT ','+ b.VCHInvoiceNbr
FROM VoucherGLDist a
JOIN Voucher b ON b.VCHVoucherNbr = a.VGLVoucher
AND b.VCHVendor = vv.VCHVendor
WHERE a.VGLJEBatch = 21174
GROUP BY b.VCHVendor,b.VCHInvoiceNbr
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROM VoucherGLDist vgl
JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher
WHERE vgl.VGLJEBatch = 21174
GROUP BY vv.VCHVendor
Curious, what if you did the following (using David Burrows code):
WITH vv (VCHVendor,VGLVoucher)
AS (SELECT DISTINCT VCHVendor,VGLVoucher
FROM VoucherGLDist vgl
JOIN Voucher v ON v.VCHVoucherNbr = vgl.VGLVoucher
--WHERE vgl.VGLJEBatch = 21174
)
SELECT vv.VCHVendor, SUM(vgl.VGLAmount) AS [VGLAmount],
STUFF((SELECT ','+ b.VCHInvoiceNbr
FROM VoucherGLDist a
JOIN Voucher b ON b.VCHVoucherNbr = a.VGLVoucher
AND b.VCHVendor = vv.VCHVendor
--WHERE a.VGLJEBatch = 21174
GROUP BY a.VGLJEBatch,b.VCHVendor,b.VCHInvoiceNbr
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROM VoucherGLDist vgl
JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher
GROUP BY vgl.VGLJEBatch, vv.VCHVendor;