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

Posted

in

by

Tags:


Related Posts

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading