How to effectively query and aggregate a normalized SQL db?
This is how I would do it.  I regularly use Coalesce to place rows into delimeted fields and it always performs and scales well (as long as you realize that a subquery is ALWAYS going to cause some performance hit).

If you didn't like running it as a stored procedure you could also easily rewrite as a table valued function.

Another approach would be a CTE I suppose but I am not as familiar with that approach to type from scratch.

CREATE PROCEDURE GetMessageById
    @pMessageID int
AS
BEGIN

SET NOCOUNT ON;

Declare @pTo varchar(max)
Declare @pCC varchar(max)
Declare @pBC varchar(max)

SELECT @pTo = COALESCE(@pTo + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 1 /** or whatever the id of TO field is */

SELECT @pCC = COALESCE(@pCC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 2 /** or whatever the id of CC field is */

SELECT @pBC = COALESCE(@pBC + ', ', '') + [EmailAddress]
FROM MessageRecipient
WHERE MessageID = @pMessageID AND RecipientTypeID = 3 /** or whatever the id of BCC field is */

SELECT Message.*, @pTo as [ToField], @pCC as [CCField], @pBC as [BCCField], (SELECT TOP 1 [EmailAddress] FROM MessageRecipient Where RecipientTypeID = 0 /**&lt;sender id&gt;*/ AND MessageID = @pmessageID) AS [FromField] FROM Message Where Message.ID = @pMessageID

END
GO


You might ask yourself how Coalesce works when used in this way ( I did, when I first saw it used ).  Basically it creates a recursive query returning each subsequent non-null value in the set, in turn, until the end of the return set.  Coming out the other end you get a coma delimited list of all results as a single string.