How to Concatenate Multiple Rows Into One Row in TSQL
DZone's Guide to
How to Concatenate Multiple Rows Into One Row in TSQL
by
Join the DZone community and get the full member experience.
Join For FreeThe 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
Like This Article? Read More From DZone
Topics:
Published at DZone with permission of Merrick Chaffer , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}