In this article I will explain with an example, how to use the SQL Server COALESCE function to select column values in Table as comma separated (delimited) string 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
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
From: http://www.aspsnippets.com/Articles/Select-Column-values-as-Comma-Separated-Delimited-string-in-SQL-Server-using-COALESCE.aspx