How to get rows into comma separated values column in sql
How to get rows into comma separated values column in sql. Fetching rows values into comma separated column value is required lots of time in our projects. So I tried to achieve this and lastly found one solution. Use this to get all row values into one single column value with comma separation.
Fetching rows values into comma separated column value is required lots of time in our projects. So I tried to achieve this and lastly found one solution.For Example :
We 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
To achieve this, lets start with below example:
Create a table named TABLE1 with one field named Name and set varchar as datatype.
Code :
Create TABLE TABLE1 ([Name] Varchar(30) )
Now insert 4 country into the newly created table.
Code :
INSERT INTO TABLE1 VALUES ('India')
INSERT INTO TABLE1 VALUES ('USA')
INSERT INTO TABLE1 VALUES ('Japan')
INSERT INTO TABLE1 VALUES ('China')
We will first create xml and then with the help of stuff We will create comma separated value:
SELECT STUFF(( SELECT ', ' + [Name] FROM (SELECT [Name] FROM TABLE1) AS T FOR XML PATH('') ) ,1,1,'') AS [Name]
and output will be :
Name
------------------------------------
India, USA, Japan, China
Thank You.
Reference: http://dotnetsquare.com/resources/4-How-to-convert-rows-into-comma-separated-values-column
Follow