How to Concatenate Multiple Rows Into One Row in TSQL
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
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)