hierarchal data problem

Results 1 to 2 of 2

Thread: hierarchal data problem

  1. #1
    Join Date
    Dec 1969

    Default hierarchal data problem

    Does anyone know how to write a stored procedure that will take for example all the customer orders (OrderID) for each customer and place the (OrderID) in one row instead of having the query return hierarchal data. Basically I want to represent each customer and in the same record in a field called Customer Orders list all the orderID&#039;s but not have it nested.<BR><BR>I am not sure but I thought maybe to loop through the orders table for each customer and store the id&#039;s in a variable then select the variable.<BR><BR>Example<BR>Customer OrderID Returned<BR>Joe Bob 27,19,89<BR>

  2. #2
    Join Date
    Dec 1969

    Default first thing i'd try

    How is your database arranged? Customer table and Order Table? Or are all the orders in the customers table (which would be bad). If it&#039;s to separate tables, then read on:<BR>I&#039;m not an expert, but the first thing I would try would be to use an inner join and write it out in a loop, using a variable named LastCustomer which checks to see if the current customer is equal to the last customer in the loop. If it&#039;s not the same, write out the customer name and the OrderID. If it is the same, then only write out the OrderID.<BR><BR>sql = "SELECT c.CustomerID AS cID, c.CustomerName AS cName, o.OrderID AS oID FROM Orders AS o INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID"<BR>Set rs = conn.execute(sql)<BR><BR>lastcustomer = rs("cID")<BR>response.write(rs("cName")&"__")<BR>d o while not rs.eof<BR> if lastcustomer &#060;&#062; rs("cID") then<BR> response.write("<BR>"&rs("cName")&"__"&rs("oID")&" , ")<BR> else<BR> response.write(rs("oID")&", ")<BR> end if<BR> lastcustomer = rs("cID")<BR> rs.movenext<BR>loop<BR><BR>~~Chaotix

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts