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.
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.
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]
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.