Using COALESCE to Build Comma-Delimited String

The new and improved approach can create the same resultset with a single SELECT statement. The following code shows how it’s done.

DECLARE @EmployeeList varchar(100)
 SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList

The COALESCE function performs the magic here. When @EmployeeList is NULL (the first row processed), it returns an empty string. On subsequent rows, it concatenates the @EmployeeList value with a comma and the current @Emp_UniqueID value.