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.

SELECT 
	customer.[CustomerID]
	,customer.[FirstName]
	,customer.[LastName]
	,SUBSTRING((
		SELECT 
			', ' + 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);