• 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;