How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field? [duplicate]

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

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

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

Note that Matt's code will result in an extra comma at the end of the string; using COALESCE (or ISNULL for that matter) as shown in the link in Lance's post uses a similar method but doesn't leave you with an extra comma to remove. For the sake of completeness, here's the relevant code from Lance's link on sqlteam.com:

DECLARE @EmployeeList varchar(100)SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +     CAST(EmpUniqueID AS varchar(5))FROM SalesCallsEmployeesWHERE SalCal_UniqueID = 1

I don't belive there's a way to do it within one query, but you can play tricks like this with a temporary variable:

declare @s varchar(max)set @s = ''select @s = @s + City + ',' from Locationsselect @s

It's definitely less code than walking over a cursor, and probably more efficient.