help with query

davieboy_xr · #1 (**permalink**) 04-29-04, 16:35

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

Pat Phelan · #2 (**permalink**) 04-29-04, 23:25

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

Nocopy · #3 (**permalink**) 05-02-04, 19:13

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.

r937 · #4 (**permalink**) 05-02-04, 19:33

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

Nocopy · #5 (**permalink**) 05-02-04, 19:40

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.