I’ve come across a few solutions to creating comma-delimited lists in SQL Server. The two most common solutions include CURSORS/WHILE loops and COALESCE. CURSORS and WHILE loops are a performance drag so I won’t even go there. COALESCE can work but the function requires variable declarations and function encapsulation if you want to return more than one record with delimited results without looping. The approach I prefer is FOR XML PATH. Below is an example using the AdventureWorks database. I show a comma-delimited list of address types for 3 customers.
', ' + customer_address.AddressType
FROM [SalesLT].[CustomerAddress] AS customer_address
WHERE customer_address.CustomerID = customer.CustomerID
FOR XML PATH('')), 3, 200000) AS AddressTypes
FROM [SalesLT].[Customer] AS customer
WHERE customer.CustomerID IN (29544,29545,29559);