Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Crosstab-like query or Sybase's LIST function in Oracle ??

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

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 2 Sep 1999 18:19:17 +0200
Message-ID: <7qm83d$bfs$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 <7qm0rn$h9e$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 Thu Sep 02 1999 - 11:19:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US