Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?

Question

Suppose we have a table with the following data:

ClientID    ClientName
3           Sowrabh Malhotra
4           Saji Mon
6           Sajith Kumar
7           Vipin Job
8           Monoj Kumar

We need to concatenate the ClientName column like the following:

Sowrabh Malhotra, Saji Mon, Sajith Kumar, Vipin Job, Monoj Kumar

Answer 1

If you're using SQL Server 2005, you could use the FOR XML PATH command.

SELECT [CustomerID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (CustomerID = Customer.CustomerID) 
         FOR XML PATH ('')), 1, 2, '')) AS Locations
FROM [Customer]

It's a lot easier than using a cursor, and seems to work fairly well.