RSS

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

24 May

 

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:-

Requirment

I have table as given below

 comma sep

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+’, ‘ ,”) + 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

Hope this will help you to solve your problem.

Advertisements
 
Leave a comment

Posted by on May 24, 2013 in SQL Query

 

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: