How to make a string out of a list (or a set of rows) MS SQL
You’ve got table #t1:
| InvoiceNumber | StockCode |
| Inv001 | Stk001 |
| Inv001 | Stk002 |
| Inv001 | Stk003 |
| Inv002 | Stk001 |
| Inv002 | Stk003 |
But you want:
| InvoiceNumber | StockList |
| Inv001 | Stk001,Stk002,Stk003 |
| Inv002 | Stk001,Stk003 |
Then use:
SELECT
DISTINCT t2.InvoiceNumber,
SUBSTRING((Select‘,’+RTRIM(t1.StockCode)AS[text()]
FROM #t1 t1
WHERE t1.InvoiceNumber = t2.InvoiceNumber
ORDER BY t1.InvoiceNumber,StockCode
For XML PATH (”)),2, 1000)[StockList]
FROM #t1 t2;