Show select results of 2 columns one to many as one comma delimited ro

[mcgibbong]

I have a table accts in Oracle 8i from which I want to select cust and acctno. Each customer can have more than 1 acctno. So select cust,acctno order by cust,acctno from accts returns
cust acct
1 989
1 990
2 010
2 011
2 012
3 001
4 800
etc.

I can product a comma delimited file from this okay, but I want one that has all acctno's with 1 cust ie

1,989,990
2,010,011,012
3,001
4,800
etc.

Apart from writing a function with a cursor, is there any neat way to do this?

 

[nagornyi]

Here is how you can do it without the function. Customers with more then 3 accounts can be added to the query the same way.

select  --- customers with 1 account
  a1.cust ||' '||a1.acctno acct_line
from 
  accts a1
where
    a1.cust in (select cust from (select cust, count(acctno) cnt from accts group by cust) where cnt=1)

union --- customers with 2 accounts

select 
  a1.cust ||', '||a1.acctno||', '||a2.acctno acct
from 
  accts a1
, accts a2
where
    a1.cust = a2.cust
and a2.acctno>a1.acctno
and a1.cust in (select cust from (select cust, count(acctno) cnt from accts group by cust) where cnt=2)

union --- customers with 3 accounts

select   
  a1.cust ||' '||a1.acctno||', '||a2.acctno||', '||a3.acctno
from 
  accts a1
, accts a2
, accts a3
where
    a1.cust = a2.cust
and a1.cust = a3.cust
and a2.acctno>a1.acctno
and a3.acctno>a2.acctno
and a1.cust in (select cust from (select cust, count(acctno) cnt from accts group by cust) where cnt=3)
=========================================================================================================