Re: rotation table in 90 degree - help

From: DStevens <dstevens_at_navidec.com>
Date: Fri, 15 Feb 2002 10:07:05 -0700
Message-ID: <a4jf7q$eh2$1@newsreader.mailgate.org>

Won't run very fast (I sure say that a lot!) :-( .....

Create or replace function ConcatMultiple (  PK IN Varchar,

 PKType IN Varchar,
 ColCat IN Varchar,
 ColDel IN Varchar,

 PKVal IN Varchar,
 TabName IN Varchar,
 OrderBy IN Varchar)
Return Varchar is
Column1 varchar2(500);
ReturnVal varchar2(2048);
sqltext varchar2(500);
c_handle Integer;
bogus Integer;
Begin
-- Construct the sql statement.
ReturnVal := NULL;
sqltext := 'Select ' || ColCat || ' FROM ' || TabName || ' WHERE ' || PK || '=';
IF (upper(PKType) in ('NUMBER', 'INTEGER')) THEN  sqltext := sqltext || PKVal;
ELSIF (upper(PKTYPE) = 'DATE') THEN
 sqltext := sqltext || 'to_date(' || chr(39) || PKVal || chr(39) || ')';

ELSE
 sqltext := sqltext || chr(39) || PKVal || chr(39); END IF;
sqltext := sqltext || ' ORDER BY ' || OrderBy; c_handle := dbms_sql.open_cursor;
dbms_sql.parse(c_handle, sqltext, DBMS_SQL.NATIVE); dbms_sql.define_column(c_handle, 1, Column1, 500); bogus := dbms_sql.execute(c_handle);
LOOP
 begin
-- The loop could be written differently.. this next statement fetches the next row.
 If dbms_sql.fetch_rows(c_handle) = 0 THEN   EXIT;
 ELSE
  dbms_sql.column_value(c_handle, 1, Column1);   IF (ReturnVal IS NULL) THEN
   ReturnVal := Column1;
  Else
   ReturnVal := ReturnVal || ColDel || Column1;   End If;
 End If;
 exception when others then
  EXIT;
 end;
End LOOP;
dbms_sql.close_cursor(c_handle);
Return(ReturnVal);
end;

Create table bogus2 (Column1 char(1), Column2 number);

insert into bogus2 values('1',100);
insert into bogus2 values('2',500);
insert into bogus2 values('3',150);
insert into bogus2 values('2',300);
insert into bogus2 values('2',350);
insert into bogus2 values('1',200);

Select column1, ConcatMultiple (

 'column1',
 'CHAR',
 'COLUMN2',
 ' ',

 column1,
 'Bogus2',
 'column2') from (select distinct column1 from bogus2);

SQL> set heading off
SQL> set lines 20000
SQL> /

1 100 200
2 300 350 500
3 150

ori wrote:

> 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
Received on Fri Feb 15 2002 - 11:07:05 CST