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.orderid

Say 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...

MikeAre you prepared to use Analysis Services?|||Look at this. You may want to put it into a function, but this is the general idea

declare @.s varchar(8000)
select @.s = b.product_name + ', ' + COALESCE(@.s, '')
 from orders a, order_items b
 where a.orderid = b.orderid
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
select @.s

|||Thanks for that... that works fine but I need to fine tune it a bit. At the moment it gives me all products for all orders whereas I need it to give me just the product names for each unique order. I tried using a "GROUP BY a.orderid" but it won't let me use the product_name column in this way.

I also want to be able to select the columns I need from the first table such as orderid, order_date etc.

I've experimented with both but can't seem to really get it to work... a little more help would be much appreciated!

Cheers,

Mike

PS: pkr - no I can't really use Analysis Services as this is part of a stored procedure for a web app that also has to run on Oracle so it needs to be fairly standard ANSI SQL.|||Assuming you don't know how many products you've got for a an order its difficult to write a single query. This is my suggestion.
1. Create a temp table with the OrderID and a "csv" text column, defaulted to ''
2. Insert the unique set of orderids into the temp table
3. Run a query that UPDATEs the csv column with itself plus the "," + product name|||Thanks pkr... in the end I wrote a function which is passed the order id and reads the values of the products into a cursor. It then builds the string of product names and returns it. It seems to work very well and I've learnt quite a lot about SQL functions that I didn't know before. I'm not at work so I can't post it but I will on Monday so possibly someone in the future can see how to do this.

Thanks for the help.

Mike|||I use that code within a function and it works fine. Forgot to suggest that. I would stay away from cursors unless you have to use them. There is a significant performance hit. I'd use the query above and avoid the cursor.|||Replacing cursors is nearly always a good idea. However, be careful with funcs, you can basically end up doing the same thing as a cursor. If you code it "incorrectly" the function will run for each row in the set, therefore the perf will be like a cursor anyway!|||Luckily the table I run the function on will not have that many rows in it at one time. I also looked at the stats for a few orders and it does only seem to be reading the ones it needs rather than processing the whole table which is good.

Will post the function when I get to work today... then people can tell me if could do it any better.

Mike.