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