This sort of thing is really best handled at the client end.

If you are using SQL Server, there is an undocumented, unsupported trick you
can use.  This trick is in fact documented to be undocumented and
unsupported ;-)  The problem is that is can be unreliable in certain query
situations; see the MS Knowledgebase article Q287515.

First, we'll construct a stored procedure fragment which will construct the
ItemsOrdered.Description columns for a given OrderID concatenated together
to give you an idea of how the trick works:

	declare @ItemsDescr varchar(1000);
	set @ItemsDescr='';
	SELECT @ItemsDescr = @ItemsDescr + ', ' + Description FROM ItemsOrdered
  		WHERE OrderID=@OrderID
		ORDER BY Description;
	set @ItemsDescr=Right(@ItemsDescr, Len(@ItemsDescr) - 2);

This will concatenate all the Description column values from ItemsOrdered
together.  The reason it's unreliable is that the query optimizer can and
may unpredictably change the order in which the SELECT statement processes
things depending on what you might put into the ORDER BY clause, or if you
have a SELECT DISTINCT, etc., so you may get the description list in a
'funny' order.  Read the article.

If you are willing to take the chance on using this approach, turn the above
procedure into a user function (for SQL Server 2000), and execute it on each
row

	SELECT OrderID, Customer, MyFunction(OrderID) as Items
		FROM Orders INNER JOIN ItemsOrdered ON blah blah...

Having spouted the politically correct line regarding undocumented tricks,
the fact is that I have used this technique, and as long as you behave
yourself and not get too carried away with embellishing the query that it's
contained in, the trick does work, and probably will in future releases -
but you never know ... ;-)

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Mike Deck [mailto:MichaelBDeck@t...]
Sent: Friday, August 02, 2002 9:14 PM
To: sql language
Subject: [sql_language] Aggregate string concatination


My basic problem is this:

I have two tables, Orders and ItemsOrdered

Orders:
OrderID
Customer
etc.

ItemsOrdered:
ItemID
OrderID (FK to Orders)
Description
etc.

I want to write a query that will return one row for every order and have
one of the columns be a list of the items in the order (i.e. concatinate
ItemsOrdered.Description where ItemsOrdered.OrderID is the same).  It
would be like using SUM to calulate the total price for each order based
on the price of each item except Description is a string not a number.

For instance, the result set might look something like this:

OrderID     Cusomer      Items
1           Joe          Item1, Item2, Item3
2           Bill         Item4, Item5, Item6, Item7
3           Rob          Item8

The commas would be nice, but not necessary

I was thinking I could do this with a group by clause and use some type of
aggregate function to concatinate all the strings, but it seems no such
function exists.  Does anyone know of a way to do this without cursors?

Thanks in advance,
Mike