Monday, 20 February 2012

How to concatenate multiple rows into one row in TSQL

The following query will achieve this quite nicely.

            '; ' + COALESCE (Surname + ',' + Forename + ' ' + MiddleInitial ,  Surname + ',' + Forename, Surname)
            FROM Person
            FOR XML PATH('')),

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

No comments:

Post a Comment

Thames water collapsed drain cover

15/10/2018 After fixing our sewage drain cover, there appears to be a mini sinkhole occurring around our Thames Water meter cover. We’re w...