STUFF AND FOR XML PATH for String Concatenation in Sql Server

In this article we will learn about STUFF AND FOR XML PATH for String Concatenation in Sql Server. SQL Server supports XML data using the FOR XML clause. We can easily convert existing data into the XML format using this. You can use the FOR XML PATH SQL Statement to join or concatenate multiple column values into single row. Please read our previous article Formatting number to add leading zeros in SQL Server.

In this article I will explain a very useful topic about how to string Concatenation using Stuff and XML path. If you are a developer then there one must be a scenario that we need this type of requirement.

We can use XmlPath(”) to concatenate column data into single row. Stuff is used to remove the first after string concatenation. 

declare @Test Table(sno int,scity varchar(20))
Insert @Test(sno,scity) Values (1,'Chicago'),(1,'Detriot') ,(2,'Memphis'),
                               (2,'Nashville') ,(3,'New York'),(3,'Dallas') ,(4,'Atlanta'),(4,'Houston')

select distinct sno ,
STUFF((Select ','+Scity
from @Test T1
where T1.sno=T2.sno
FOR XML PATH('')),1,1,'') from @Test T2

Conclusion

So far this article we learnt about STUFF AND FOR XML PATH for String Concatenation in Sql Server.