Convert Multiple Rows into One Comma Separated Values in SQL server 2008

Convert Multiple Rows into One Comma Separated Values in SQL server 2008

Solutions

For the above problem, below are different queries to achieve this.

Query 1: Using “COALESCE” function

DECLARE @temp VARCHAR(MAX)
SELECT @temp = COALESCE(@temp+’, ‘ ,”) + title
FROM [dbo].[country]
SELECT @temp ‘title’

Query 2: Using without “COALESCE” function

DECLARE @temp VARCHAR(MAX)
SET @temp = ”
SELECT @temp = @temp + title + ‘, ‘
FROM [dbo].[country]
SELECT SUBSTRING(@temp, 0, LEN(@temp))

Query 3: Using “FOR XML PATH”

DECLARE @temp VARCHAR(MAX)
SET @temp = (SELECT ‘, ‘ + cast(s.title as varchar)
FROM [dbo].[country] s
ORDER BY s.title
FOR XML PATH(”))
SELECT SUBSTRING(@temp, 2, 200000) AS title