Re: rotation table in 90 degree

From: Igor Izvekov <igoriz_at_cmtk.net>
Date: 15 Feb 2002 10:15:09 -0800
Message-ID: <9f17469e.0202151015.4c403401@posting.google.com>

ori_m57_at_hotmail.com (ori) wrote in message news:<479fe5cd.0202150035.1819a96b_at_posting.google.com>...
> Hi,
>
> I need to create a view that looks like a 90 degree rotation of the
> original table.
> for example: say my table looks like:
> A B
> 1 100
> 2 500
> 3 150
> 2 300
> 2 350
> 1 200
>
> then my view should look something like:
> 1 100 200
> 2 500 300 350
> 3 150
>
> I cant know how many rows in the table contain the same A data.
> can I create a view like this?
>
> thanks alot
> Ori

Why don't you create function something like : create or replace function ninety(tbl IN VARCHAR2,col1 IN VARCHAR2,col2 IN VARCH
AR2,prime in VARCHAR2) RETURN varchar2 IS res varchar2(4000):=''; -- result returned query varchar2(4000); -- general purpose string crs INTEGER; -- dynamic cursor
dummy INTEGER; -- just a dummy to get execute function result begin

   crs:=DBMS_SQL.OPEN_CURSOR;
   query:='SELECT '||col2||' FROM '||tbl||' WHERE '||col1||'='||prime;    DBMS_SQL.PARSE(crs,query,DBMS_SQL.V7);    DBMS_SQL.DEFINE_COLUMN(crs,1,query,4000);    dummy:=DBMS_SQL.EXECUTE(crs);
   while DBMS_SQL.FETCH_ROWS(crs)>0
   loop

      DBMS_SQL.COLUMN_VALUE(crs,1,query);
      res:=res||rpad(query,5);

   end loop;
   DBMS_SQL.CLOSE_CURSOR(crs);
   return res;
end;
/
show errors

and then execute a query, something like this :

select distinct a,ninety('my_table','a','b',a) b from my_table;

If you know for certain that only one table will always be used in queries, then "ninety" function could be made much simpler without dynamic SQL.

Regards, Igor Izvekov. Received on Fri Feb 15 2002 - 12:15:09 CST

Re: rotation table in 90 degree - help