Rows to a List

From: mike <hillmw_at_charter.net>
Date: 3 Aug 2005 10:21:49 -0700
Message-ID: <1123089709.151587.163180@g49g2000cwa.googlegroups.com>

I have a many to one relationship where I'd like to convert the rows to a list format.

I currently use a function, but using that function slows the query down.

I'd like to improve on that performance.

example schema:

tbl_x: num,flda,fldb,fldc as
1, me, you, them
2, me, you, them
3, me, you, them

tbl_y: num,fldz as

1, fred
1, lisa
1, dave

My function is:
create or replace function list ( field1 number ) return varchar2 as

   res varchar2(2000) := null;
   first boolean := true;
begin

   for curs in

       (select fldz from tbl_y where num=field1 order by fldz)    loop

      if not first then
         res := res || ',';
      else
         first := false;
      end if;
      res := res || curs.fldz;

   end loop;
return res;
end list;

My sql then is:

select num,flda,fldb,fldc,list(num)
from tbl_a

I should get:

'1', 'me', 'you', 'them', 'fred,lisa,dave'

Any ideas to reduce performance?

Thanks, Mike Received on Wed Aug 03 2005 - 12:21:49 CDT