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