I did something similar concatnating room#s for a equipment planning program
for new hospitals and hospital building add ons.   The database report
showed each medical device bought under the purchase plan and the rooms in
the hospital where the equipment would be used.
I used temp tables and recursive loops to do the job of listing unique
medical device types, with specifications and have a field in that row that
all the rooms that device type would be used.
1. create a temp table "#Orders" where  all the order/customer detail would
be stored - don't forget to include a colume (id) that is integer
identity(1,1) - this is a autonumber field that numbers each order, and a
field "Items" that will hold the concatenated items string - be sure to make
it big enough to hold all the items-. Next create a #This table when first
filled with the order details(not the items string yet) will be used in the
outer recursive loop.  Now declare a integer variable (@I integer) that you
will set to (select max(id) from temp_1).   Now start the outer loop with
While @I  > 0
BEGIN
Declare a variable to hold the string of items - such as @Str varchar(3000).
The idea is to do the inner recursive loop until all of the items of a
particular order are strung to geather in the @Str variable.   The next item
after decrementing the inner variable but before decrementing the outer
variable is to update the Items column for the first order with the @Str
variable.   After the update, set @Str to an empty string ('') and decrement
the outer variable to start the outer loop on the next order ID.
Summarizing: the outer loop loops thru the orders while the inner loop loops
thru the items for the outer loops order concatinating each of the items for
that order in the @Str variable. The inner string then passes control to the
outer loop but before to begins again. the temp_1 table column "Items" must
be updated with the value of the @Str variable, and then the outer loop
counter @I must be decremented by one @I = @I - 1.   The same process starts
again from the outer loop but for the next order - this proceeds until all
the orders have been looped thru.



This is the point where you will
----- Original Message -----
From: "sql language digest" <sql_language@p...>
To: "sql_language digest recipients" <sql_language@p...>
Sent: Friday, August 02, 2002 7:00 PM
Subject: sql_language digest: August 02, 2002


> -----------------------------------------------
> When replying to the digest, please quote only
> relevant material, and edit the subject line to
> reflect the message you are replying to.
> -----------------------------------------------
>
> The URL for this list is:
> http://p2p.wrox.com/list.asp?list=sql_language
> SQL_LANGUAGE Digest for Friday, August 02, 2002.
>
> 1. Aggregate string concatination
> 2. RE: Aggregate string concatination
>
> ----------------------------------------------------------------------
>
> Subject: Aggregate string concatination
> From: "Mike Deck" <MichaelBDeck@t...>
> Date: Fri, 2 Aug 2002 21:13:32
> X-Message-Number: 1
>
> 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
> ----------------------------------------------------------------------
>
> Subject: RE: Aggregate string concatination
> From: "Jeff Mason" <jeffm.ma.ultranet@r...>
> Date: Fri, 2 Aug 2002 17:01:21 -0400
> X-Message-Number: 2
>
> 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
>
>
>
>
> ---
>
> END OF DIGEST
>
>