Sunday, October 12, 2008

Using COALESCE to Build Comma-Delimited String

COALESCE is new function in SQL Server 2005. This is used to create comma-separated value string i.e to 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

--Results--

---------
1, 2, 4, 5

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.

No comments: