Return Comma Separated Value from a Table In Select Query

Suppose I have a table like below:

Image 1.

And you need to return all name as a comma separated string, Like below

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

Use below Command:

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

Image 2.

If you want to put a condition then

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

Image 3.

I you want to show your result group by any column then use below 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

 

Image 4.