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
