Return Coma Separated Value From a Table

Suppose I have a table as in the following:



And you need to return all the names as a comma separated string, as in the following:

Rahul Saxena,Sara Sinha,Priyanka Mathur,Shambhu Sharma,Manu Khanna,Ganga,Shweta,Shraddha,Akhilesh,Mayank,Rakesh,Abhishek,Saurabh

Use the following command:

SELECT SUBSTRING((
SELECT ',' + CAST(Name AS VARCHAR) FROM Employee
FOR XML PATH('')), 2,10000) AS NAME

If you want to provide a condition then:

SELECT SUBSTRING((
SELECT ',' + CAST(Name AS VARCHAR) FROM Employee WHERE Country='India'
FOR XML PATH('')), 2,10000) AS NAME

I you want to show your result group by a column then use the following query:

SELECT DISTINCT Country,
STUFF(
(
SELECT ',' + CAST(Name AS VARCHAR)
FROM Employee AS t2
WHERE t2.Country = t.Country
FOR XML PATH('')
), 1, 1, '') AS id_list
FROM Employee AS t