Sql server: different approaches to create comma separated list

In this article I am going to explain how to create comma separated list in Sql server.

In previous article I have explained how to Encrypt store procedure, view andfunction in sql server, how to take database Backup and restore using commandT-Sql and sql server REPLACE function.

 Description:

I want to show values of column in comma separated list. E.g. below attached screenshot show the data of city table and want to show city name comma separated. 

Method 1:

DECLARE @delimitor VARCHAR(MAX)

SET @delimitor = ''

SELECT  @delimitor = CityName + ','+ @delimitor FROM Tb_City

select @delimitor as [cityname]

Method 2: using coalesce

declare @cityname varchar(max)

  select @cityname=coalesce(@cityname+',','')+CityName from Tb_City order by cityname 

  select @cityname as [cityname]

Method 3: using xml path

SELECT  STUFF((SELECT ', ' + CAST(CityName AS VARCHAR(100))FROM Tb_City group by CityName

FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') as [cityname]

 Output: