Sql server: different approaches to create comma separated list | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF 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

  

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget