Return Coma Separated Value From a Table

Introduction

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  

If 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