Thursday, January 12, 2017

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.


 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. 

Sql server: different approaches to create comma separated list

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:

Sql server: different approaches to create comma separated list

  

No comments:

Post a Comment