Goup together multiple invoices

Question

One payment can have more than one invoice, so querying table or result set may bring back more than one row.

How do I stop this ?

Customer Name   Payment no   Invoice No
-------------   ----------   ----------    
Dave A          1            10
Dave T          2            10
Dave T          2            12

So I want:

Customer Name   Payment no   Invoice No
-------------   ----------   ----------    
Dave A          1            10
Dave T          2            10, 12

This has been asked so many times here. Do a quick search with the tags [sql-server] and [group-concat] — Lamak, Mar 07 '13 at 15:16

score 3 · Accepted Answer · answered Mar 07 '13 at 15:15

SELECT
     [Customer Name], [Payment no],
     STUFF(
         (SELECT ',' + CAST([Invoice No] AS VARCHAR(10))
          FROM TableName
          WHERE [Customer Name] = a.[Customer Name] AND
                [Payment no] = A.[Payment no]
          FOR XML PATH (''))
          , 1, 1, '')  AS InvoiceList
FROM TableName AS a
GROUP BY [Customer Name], [Payment no]

SQLFiddle Demo

Goup together multiple invoices

1 Answers1