STUFF AND FOR XML PATH for String Concatenation in Sql Server

In this blog 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