I've got a MSSQL2005 quandary and after hours of searching I can't seem to find a solution.

7 Tables in an e-commerce application:

email_list:
email_id
email_address
subscribed (0/1)
s_id (the account's shopper ID)

ems_shopper:
s_id (the account's shopper ID)
f_name

ems_cart:
b_id (the basket ID)
s_id (the account's shopper ID)
date_created

ems_cart_items:
b_id
sku
qty

ems_sku:
sku
p_id (product ID that the sku belongs to)
attr1 (an attribute like "8-oz Travel Size")

ems_price_std:
sku
shopper_type = "RETAIL"
price

ems_product:
p_id
p_name
p_name_sub

Basically what I need is a recordset of all people in the email list (email_id & email_address) who are subscribed (=1) and have a shopping cart created on or after <a passed date> and what's in their cart. For each item in the cart I need:

ems_product.p_name
ems_product.p_name_sub
ems_sku.sku
ems_sku.attr1
ems_cart_items.qty
ems_price_std.price

Easy enough so far, however, I end up with a recordset that has one record per item in the qualifying email recipient's cart. So if a given email address has 5 items there would be 5 rows.

Is there a way to concatenate the line item fields into a string and store it in a field in the recordset so that each qualifying email address ends up with a single record?

email_id
email_address
f_name
lineitems

where lineitems would be a long string preformatted with HTML, something like:

<SOMEHTML>p_name1<SOMEHTML>p_name_sub1<SOMEHTML>sk u1<SOMEHTML>attr11<SOMEHTML>qty1<SOMEHTML>price1<S OMEHTML>p_name2<SOMEHTML>p_name_sub2<SOMEHTML>sku2 <SOMEHTML>attr12<SOMEHTML>qty2<SOMEHTML>price2.. . and so on.

Does that make sense?

Is that even something that can be done in a single SQL query? Would it have to be a SProc?