Comma separated list different ways

creating table with sample data.

create table CommaSeparatedList

(

ID int identity(1,1) primary key,

Name varchar(100)

)

insert into CommaSeparatedList (Name)

values('rakesh'),('raju'),('ravi')

Method-1:

Using ISNULL :

declare @commalist varchar(max)=null

select @commalist=isnull((@commalist+','),'')+cast(ID as varchar(max)) from CommaSeparatedList

select @commalist

output:

1,2,3

Method-2:

Using COALESCE :

declare @commalist varchar(max)=null

select @commalist=coalesce(@commalist+',','')+cast(ID as varchar(max)) from CommaSeparatedList

select @commalist

output:

1,2,3

Method-3:

Using FOR XML PATH :

select stuff((select distinct ','+cast(ID as varchar(max)) from CommaSeparatedList

group by ','+cast(ID as varchar(max))

for xml path('')),1,1,'')

output:

1,2,3