Create Row items as a string with Comma Separated Values in SQL Server

In this example, i will describe how to create a comma separate string using database row items. To do that, I will use AdventureWorks2008R2 SQL Server database [Production].[ProductCategory] table. I will create a comma separate string using name column in following example:

1.) Run the following query and see whether you have records in the table

SELECT *
FROM [AdventureWorks2008R2].[Production].[ProductCategory]

This will return following result as displayed below:

2.) Now run the following query to get the result. For that I have used COALESCE function. For more information about COALESCE function please refer this link.

USE AdventureWorks2008R2

--@CategoryList is variable to add the category names
DECLARE @CategoryList VARCHAR(MAX)

SELECT @CategoryList = COALESCE(@CategoryList + ', ', '') + [Production].[ProductCategory].Name
FROM [Production].[ProductCategory]

-- Returns the result
SELECT @CategoryList 

Now you can see the following result.

Download