Search code examples
sqlsql-servergroup-concat

Combine all selected columns to one column

I'm running out of solution to combine below table. Kindly assist. Thanks.

InvoiceNo FileNo
00001      A001
00002      A001

Above is the table that i select, what i want to show in my list is like below.

InvoiceNo       FileNo
00001,00002      A001

Your help is much appreciated!

Solution

  • Sql-server version

    you can use STUFF function.

    SELECT  STUFF((
          SELECT ',' + InvoiceNo
          FROM T
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') as 'InvoiceNo'      ,FileNo
FROM T
GROUP BY FileNo

    sqlfiddle:http://sqlfiddle.com/#!18/5028e/8

    Oracle version

    you can use LISTAGG function.

    SELECT 
    LISTAGG(InvoiceNo, ', ') WITHIN GROUP (ORDER BY InvoiceNo)  InvoiceNo,
    FileNo  
FROM t
GROUP BY FileNo

    http://sqlfiddle.com/#!4/5028e/2

    [Results]:

    |    INVOICENO | FILENO |
|--------------|--------|
| 00001, 00002 |   A001 |