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
Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.
View all posts by SQLWhisperer