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
Leave a Reply