The way I would choose to do it depends on the volume of data you are
looking at.

For low volume I would probably go for a user defined funtion, you
could pass the parent record in the foreign key relationship to it,
have it build you delimited string and pass it back as a varchar
field. The function call could be done as a column in your resultset.

If you are dealing with larger volumes I would most likely create a
stored procedure to get all foreign key records that correspond to
your query, created delimited strings from them and using insert exec,
write them to a temp table. Then you can join this temp table to the
original resultset. It's a little more long winded but it is far more
efficient.

James