Generate column values as comma separated in SQL SERVER

 Method 1: Using XML PATH.

We can take an example table for this :

Table name is : CommaSeperatedExampleTbl

Id         name                            age

-----------------------------------------------------

1          Arun                             24

2          Jijo                               25

3          Prasanth                       26

4          Nikhil                            26

5          Hareesh                        20

8          Santhosh                      24

10         Achu                            22

Now we are going to make the second column of the above table as comma separated using XML PATH

SELECT Stuff(

  (SELECT N', ' + cast (name as nvarchar ) FROM

                  CommaSeperatedExampleTbl

                   as a FOR XML PATH(''),TYPE)

  .value('text()[1]','nvarchar(max)'),1,2,N'')as Name

It will results :

Name

-----------

Arun, Jijo, Prasanth, Nikhil, Hareesh, Santhosh, Achu

Method  2: Using coalesce

DECLARE @str nVARCHAR(MAX) 

select @str=  coalesce(@str + ',', '') + CAST(a.name AS nVARCHAR)

FROM CommaSeperatedExampleTbl a

select @str as Name

The result will be :

Name

-----------

Arun,Jijo,Prasanth,Nikhil,Hareesh,Santhosh,Achu