Building a string of subquery results in SQL
We have a table which includes a one-to-many relationship with some second table.
Perhaps a person table which has a relationship to different email addresses associated with that person
We want to build a query to return the person's name and the email addresses as a comma separated string
Thanks to the STUFF
function we can build up this string as part of the query itself instead of having to perform a second query or hydrate the entire object graph using an ORM like Entity Framework to extract the information using LINQ.
First we select the details we want from the PersonEmail table into an XML string using the FOR XML
function
before concatenating the information into a single string using STUFF
SELECT p.Id, p.Name,
STUFF((SELECT ', ' + '(' + pe.AddressType + ') ' + pe.EmailAddress
FROM PersonEmail pe
WHERE pe.PersonId = p.Id FOR XML PATH('')), 1, 1, '') AS EmailAddresses
FROM Person p
WHERE /* ... */
So for a person with two email addresses, one work and one home we''ll get a result similar to
Id | Name | EmailAddresses |
---|---|---|
1 | Simon | (Work) simon@work.com, (Home) simon@home.com |