String concatenation using FOR XML PATH() and STUFF

In this article, we will be discussing how to convert row data to column data with ',' separation.

We can fulfill this requirement with the help of  STUFF , Substring and FOR XML PATH() very easily.



Let's create a table with sample data.


CREATE TABLE ForXmlExp (ID INT, NAME VARCHAR(250)) 


INSERT INTO ForXmlExp VALUES (1,'Ganesh') 

INSERT INTO ForXmlExp VALUES (2,'Mohan') 

INSERT INTO ForXmlExp VALUES (3,'Raju') 

INSERT INTO ForXmlExp VALUES (4,'Kiran') 

INSERT INTO ForXmlExp VALUES (5,'Charan') 

INSERT INTO ForXmlExp VALUES (1,'Rajesh') 

INSERT INTO ForXmlExp VALUES (2,'Venkat') 

select table:

Select * from  ForXmlExp 

FOR XML PATH( )  clause returns the SQL results as XML elements.
By giving empty (' ') string in PATH argument and concatenated  ','  with Name column. we will get below results.

Query:

SELECT ',' + NAME FROM   ForXmlExp FOR xml path('')

OutPut:

,Ganesh,Mohan,Raju,Kiran,Charan,Rajesh,Venkat


In the above output we can see Row data converted to the column data, but one additional ',' is there in the first position. That we can remove using STUFF , Substring functions.


Query to remove leading comma.


SELECT Stuff((SELECT ',' + NAME 
              FROM   ForXmlExp 
              FOR XML PATH('')),1,1,'') 

OR

we can write the same logic with Substring As well.


SELECT Substring((SELECT ',' + NAME 
                  FROM   ForXmlExp 
                  FOR XML PATH('')), 1, 8000) 



Output:

 Ganesh,Mohan,Raju,Kiran,Charan,Rajesh,Venkat


As of now, we worked on a particular column, Now let's work on the table and get the result based on the ID column and applying  Group By on ID Column.


Query:

SELECT ID, 
       Stuff((SELECT ',' + NAME 
              FROM   ForXmlExp T1 
              WHERE  T1.ID = T2.ID 
              FOR XML PATH ('')), 1, 1, '') AS Result 
FROM   ForXmlExp T2 
GROUP  BY ID 


Output:

Thank you for visiting the blog, hope you got the required information.