.NET Zone is brought to you in partnership with:

Merrick Chaffer is a Software Developer with Russell Reynolds Associates, with over 16 years experience in areas relating to Microsoft Application Development. Merrick is a DZone MVB and is not an employee of DZone and has posted 34 posts at DZone. You can read more from them at their website. View Full User Profile

How to Concatenate Multiple Rows Into One Row in TSQL

07.10.2012
| 4767 views |
  • submit to reddit

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, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)