Monday, 20 February 2012

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

No comments:

Post a Comment

How to fix blurry images in Google Chrome

Problem When browsing sites with thumbnail images, those images appear blurred in Chrome, but not in other browsers such as Internet Explore...