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