Home  »  Articles  »  SQL Server  »

How to convert rows into comma separated values column

 Posted By : Manoranjan SahooPosted Date : 13/10/2010 09:20:12 PM Category : SQL Server Points : 5 (Rs 1.00)

 

Converting rows values into comma separated column value is requ

ired lots of time. So i tried to achieve this and lastly found one solution.

For Example :

i have the following data in a Table called TABLE1

TABLE1 - Table name

Name - column name

India

USA

Japan

China

I want to fetch data like below :

India, USA, Japan, China

So, to achieve this, lets start with below example:

Create a table named Table1 with one field named Name and set varchar as datatype. Then insert above 4 country name into that table.

 

Create TABLE TABLE1 ([Name] Varchar(30) )

INSERT INTO TABLE1 VALUES ('India')

INSERT INTO TABLE1  VALUES ('USA')

INSERT INTO TABLE1  VALUES ('Japan')

INSERT INTO TABLE1  VALUES ('China')

I will first create xml and then with the help of stuff i will create comma separated value:

SELECT STUFF( -- Remove first comma

( SELECT ', ' + [Name] FROM -- create comma separated values

( SELECT [Name] FROM TABLE1 ) AS T FOR XML PATH('') ) ,1,1,'') AS [Name]

 

and output will be :

Name

------------------------------------

India, USA, Japan, China 

Thank You.

 

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.