STUFF AND FOR XML PATH for String Concatenation

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')

Capture12

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

Capture13