How to Concatenate Multiple Rows Into One Row in TSQL
by
Join the DZone community and get the full member experience.
Join For FreeThe 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.
Trending
-
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
Comments