Sql server: different approaches to create comma separated list - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

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

  

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon