Merge Multiple Data from Rows/Records into One Row w/ Comma Separated Fields

Question

If I were to query our ORDERS table, I might enter the following:

 SELECT * FROM ORDERS
 WHERE ITEM_NAME = 'Fancy Pants'

In the results for this query, I might get the following:

 ----------------------------------------------------------------------
 ORDER_ID       WAIST       First_Name       Email
 ----------------------------------------------------------------------
 001            32          Jason            j-diddy[at]some-thing.com
 005            28          Pip              pirrip[at]british-mail.com
 007            28          HAL9000          olhal[at]hot-mail.com

Now, I'm also wanting to pull information from a different table:

SELECT * FROM PRODUCTS WHERE ITEM_NAME = 'Fancy Pants'

 ------------------------------------------
 PRODUCT_ID     Product       Prod_Desc
 ------------------------------------------
 008            Fancy Pants   Really fancy.

In the end, however, I'm actually wanting to condense these records into one row via SQL query:

 -----------------------------------------------------------------------------
 PRODUCT       ORDER_Merged  First_Name_Merged  Email_Merged
 -----------------------------------------------------------------------------
 Fancy Pants   001,005,007   Jason,Pip,Hal9000  j-di[...].com, pirrip[...].com

Anyway, that's how it would look. What I can't figure out is what that "merge" query would look like.

My searches here unfortunately keep leading me to results for PHP. I have found a couple of results re: merging into CSV rows via SQL but I don't think they'll work in my scenario.

Any insight would, as always, be greatly appreciated.

UPDATE:

Ah, turns out the STUFF and FOR XML functions were exactly what I needed. Thanks all!!

 Select
    A.name,
         stuff((
         select ',' + B.address
         from Addresses B
         WHERE A.id=B.name_id
         for xml path('')),1,1,'')
    From Names A

Solution

This is an excellent article on various approaches to group concatenation with pro's and con's of each.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Personally however, I like the Coalesce approach as I demonstrate here:

https://dba.stackexchange.com/a/2615/1607