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
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
|1||Simon||(Work) firstname.lastname@example.org, (Home) email@example.com|