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

Mahesh Alle

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.

9 Comments

Join the conversation! Your thoughts help the community grow.

Umesh Aggarwal6y ago
Sir do you have a solution with unique values
kalai romi6y ago
Try this below example too,,,i hope it helps..
kalai romi6y ago
SELECT STUFF((SELECT ', ' + Column Name FROM Table Name FOR XML PATH('')),1,2,'') as [Column Name]
Abhilash Sengupta8y ago
I want to implement the same but using int values. Can you help me out? Thanks.
Atul Kale8y ago
I want to implement exactly same scenario in PDW server. Above queries/functions do not work in PDW. Could you please help on this?
Manav Pandya9y ago
Nice one ............
Onkar Gupta11y ago
good job sir ji
vikrant choudhary12y ago
Thanks a lot.. :)
- Mahesh Alle12y ago
  You are welcome.