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!
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 |
if mysql then use group_concat function
SELECT FileNo,GROUP_CONCAT(InvoiceNo) as InvoiceNo
FROM your_table
GROUP BY FileNo;