How Do I Do This In SS2000?

Hi all,





I have a simple query which returns all the names of products attached to a particular order.


select a.orderid, b.product_name


from orders a, order_items b


where a.orderid = b.orderidSay it returns this data: (sorry about the formatting!)





orderid product_name


===== =========


001234 Sweater (Black)


001234 Trousers (Large)


001234 T-Shirt (Pink)





What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".





I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..





TIA for any help...





Mike