Re: Rows to a List

From: Matthias Kleinicke <Matthias.Kleinicke_at_gmx.de>
Date: Wed, 03 Aug 2005 22:56:56 +0200
Message-ID: <dcraj5$i9i$1@online.de>

mike schrieb:
> 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
>

Other solutions would use fixed number of columns:

select rtrim(max(decode(rownum, 1, field, null)||','

            ||max(decode(rownum, 2, field, null)||','
              ...
            ||max(decode(rownum, n, field, null), ',')

from (select field from table order by field);

maybe there is some limit to rows possible. Received on Wed Aug 03 2005 - 15:56:56 CDT