Re: Crosstab-like query or Sybase's LIST function in Oracle ??

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 6 Sep 1999 11:05:19 +0200
Message-ID: <7r005q$n5u$1@oceanite.cybercable.fr>

You can use:

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

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

   for curs in

       (select fld2 from crosstab where fld1 = field1 order by fld2 )    loop

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

   end loop;
return res;
end list;
/

select * from crosstab;

      FLD1 FLD2
---------- ----------

         2         23
         2         22
         2         21
         1         15
         1         14
         1         13
         1         12
         1         11

8 rows selected.

select fld1, list(fld1) fld2
from crosstab
group by fld1
/

      FLD1 FLD2

---------- --------------------
         1 11, 12, 13, 14, 15
         2 21, 22, 23

2 rows selected.

Regards.

monsri_at_my-deja.com a écrit dans le message <7qm0uc$hh1$1_at_nnrp1.deja.com>...
>Hi,
>Did anybody succeed in making a crosstab-like query in Oracle ?
>For ex. if I have the following in my table:
>
>> SQL> break on fld1
>> SQL> select * from crosstab;
>>
>> FLD1 FLD2
>> ---------- ----------
>> 1 11
>> 12
>> 13
>> 14
>> 15
>> 2 21
>> 22
>> 23
>>
>> 8 rows selected.
>
>I'd like to get something looking like:
>> SQL> select fld1, list(fld2) from crosstab [group by fld1];
>>
>> FLD1 FLD2
>> ---------- ------------------
>> 1 11, 12, 13, 14, 15
>> 2 21, 22, 23
>> SQL>
>
>as it's possible to do with Sybase. Did someone ever manage getting
>that kind of stuff either in pure SQL or with a user-defined procedure
>??
>
>Thanks a lot !!
>
>Spendius
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Mon Sep 06 1999 - 04:05:19 CDT