In this article I will explain with an example, how to use the SQL Server COALESCE function to get comma separated (delimited) values from Table in SQL Server.
COALESCE function can be used to get comma separated (delimited) values from Table in the following SQL Server versions i.e. 2005, 2008, 2008R2, 2012 and 2014.
 
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Download and install Northwind Database
 
 
Get comma (delimited) separated values from Table in SQL Server using COALESCE function
The following Stored Procedure accepts two parameters, City (Input parameter) and EmployeeIds (Output parameter).
It returns the ID of all Employees based on the City. The returned Employee Ids are separated (delimited) by comma using the COALESCE function in SQL Server.
CREATE PROCEDURE GetEmployeesByCity
      @City NVARCHAR(15)
      ,@EmployeeIds VARCHAR(200) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
 
      SELECT @EmployeeIds = COALESCE(@EmployeeIds + ',', '') + CAST(EmployeeId AS VARCHAR(5))
      FROM Employees
      WHERE City = @City
END
 
 
 
Fetching comma (delimited) separated values returned values from Stored Procedure
In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
DECLARE @EmployeeIds VARCHAR(200)
EXEC GetEmployeesByCity 'London', @EmployeeIds OUTPUT
SELECT @EmployeeIds
 
Output
COALESCE Example - Get comma (delimited) separated values from Table in SQL Server
 
You can also make use of the Split function to split the comma separated (delimited) values into rows.
DECLARE @EmployeeIds VARCHAR(200)
EXEC GetEmployeesByCity 'London', @EmployeeIds OUT
SELECT Item AS EmployeeId FROM dbo.SplitString(@EmployeeIds, ',')
 
Output
COALESCE Example - Get comma (delimited) separated values from Table in SQL Server