Convert rows into comma separated values column – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags , with Leave a comment on Convert rows into comma separated values column – SQL Server

Convert rows into comma separated values column – SQL Server

Converting rows values into comma separated column value is required lots of time:

example, i want to convert :

Name
------------------------------
India
USA
Japan
China
Switzerland

to

Name
------------------------------------
 India, USA, Japan, China, Switzerland

so, to achieve this, lets start with below example:
Create a table datatype and insert values into this:

DECLARE @Country TABLE (id INT, [Name] Varchar(30) )

INSERT INTO @Country VALUES (1,'India')
INSERT INTO @Country VALUES (2,'USA')
INSERT INTO @Country VALUES (3,'Japan')
INSERT INTO @Country VALUES (4,'China')
INSERT INTO @Country VALUES (5,'Switzerland')

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 @Country --Your query here
	) AS T FOR XML PATH('')
)
,1,1,'') AS [Name]

and output will be :

Name

------------------------------------
 India, USA, Japan, China, Switzerland

Related posts