Generating Comma Seperated List Using SELECT Clause From Table Column

Following script will Generate comma separate values or comma separate list from tables.


Method 1):
DECLARE @CSV VARCHAR(MAX)
SELECT @CSV= COALESCE(@CSV+ ',', '') + < Column Name >
FROM <TableName >
WHERE <Condition >
SELECT @CSV


Method 2) :
DECLARE @Result AS VARCHAR(2000)
SET @Result = ''
SELECT @Result = < Column Name >+', '+ @Result
FROM <TableName >
WHERE <Condition >

SET @Result=SUBSTRING(@Result,0,LEN(@Result))
SELECT @Result


Example:
Below query gives "CourseName" as a table

SELECT CourseName
FROM Courses

Output:

CourseName
------------
ASP.NET
SQL Server 2005
Java
MVC

If you give the following query, it will returns the data as a comma seperated values

DECLARE @CSV VARCHAR(MAX)
SELECT @CSV= COALESCE(@CSV+ ',', '') + CourseName
FROM Courses
SELECT @CSV

Output:
ASP.NET,SQL Server 2005,Java,MVC


In the above methods First method is the efficient one. Because

COALESCE function deals with the null values.

COALESCE() :
According to MSDN, coalesce returns the first non-null expression among its arguments.
COALESCE() accepts a series of values and a value to use in the event that all items in the list are null; then, it returns the first not-null value

Read More»