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. The problem and solution is as below:-

Problem

I have table as given below

Image.jpg

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.
 

Ebook Download
View all
Learn
View all