Results 1 to 7 of 7

Thread: help with query

  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unanswered: help with query

    hi all
    Need a bit of help/direction with an sql query
    i have a table with customer details and in another with customer orders. I need to show all the orders in the same field based upon the id from the customer details table.In other words to piggy back all the orders in one field.Do i use some sort of sub query?
    if i have :
    Code:
    customer orders:
    
    custID     Order
    c1           Vauxhall
    c1           ford  
    c1           VW
    c1           BMW
    so that i get:
    Code:
    customer              car orders
    c1                    Vauxhall ford VW BMW




    How can i achieve this? thanks dave
    Last edited by davieboy_xr; 04-29-04 at 17:42.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The best way to do a pivot is using the client software. It can be done reasonably cleanly on the server using database engine specific code. It can also be done with pure SQL, but that requires some assumptions and some rather ugly code.

    The short answer is: you should really do this on the client.

    -PatP

  3. #3
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Why not write a function that returns a list of orders and use it in the select list.
    create function get_orders(i_custid VARCHAR2) RETURN VARCHAR2
    IS
    retval VARCHAR2(999);
    BEGIN
    for rec in (select order from customer_orders where custid = d_custid)
    loop
    retval := retval || ' '|| order;
    end loop;
    return retval;
    end;

    select distinct custid customer, get_orders(custid) "car orders"
    from customer_orders;

    Now, that's oracle specific but you get the gist.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in sybase asa, use the list function:
    Code:
    select customers.custID
         , list(custorder)
      from customers
    left outer
      join orders
        on customers.custID
         = orders.custID
    group 
        by customers.custid
    in mysql 4.1, use the group_concat function:
    Code:
    select customers.custID
         , group_concat(custorder)
      from customers
    left outer
      join orders
        on customers.custID
         = orders.custID
    group 
        by customers.custid
    in other less advanced databases, write a program

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Arrr, maybe we ought to start with why would you ever need this?
    If I was to design my own DBMS I would have put this type of function on the LRU list and flush out of SGA ASAP.

  6. #6
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    thanks.. all your help is much appriciated

  7. #7
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by davieboy_xr
    thanks.. all your help is much appriciated
    What DB are you on anyways?
    My way or the highway. Yeah

Posting Permissions

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