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

I have come across a problem while doing development. Finally I have sorted this problem. So I have decided to write an blog on this.

I have come across a problem while doing development. Finally I have sorted this problem. So I have decided to write an blog on this. The problem and solution is as below:-

Problem

I have table as given below

Now I want to convert state row values to one string value.

Ex: Andhra Pradesh, Arunachal Pradesh, Assam, Maharashtra

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+', ' ,'') + state
FROM [dbo].[country]
SELECT @temp

Query 2: Using without "COALESCE" function

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

Query 3: Using "FOR XML PATH"

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

Hope this will help you to solve your problem.