How to Concatenate Multiple Rows Into One Row in TSQL
Join the DZone community and get the full member experience.Join For Free
The following query will achieve this quite nicely.
SELECT STUFF( (SELECT TOP 10 '; ' + COALESCE (Surname + ',' + Forename + ' ' + MiddleInitial , Surname + ',' + Forename, Surname) FROM Person FOR XML PATH('')), 1, 2, '')
The important pieces of this query are that we're using the STUFF function to remove the starting '; ' from the resulting string, and also using FOR XML PATH('') to get the results of the inner SQL select into a single row result set instead of 10 rows.
Results come back in the format...
Surname1, Forename1; Surname2, Forename2; Surname3, Forename3
Published at DZone with permission of Merrick Chaffer, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Boosting Application Performance With MicroStream and Redis Integration
Five Java Books Beginners and Professionals Should Read
Building and Deploying Microservices With Spring Boot and Docker
File Upload Security and Malware Protection