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
So far this article we learnt about STUFF AND FOR XML PATH for String Concatenation in Sql Server.