Skip to Main Content
  • Questions

  • PIVOT with dynamic number of columns and rows

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Apostolos.

Asked: May 07, 2012 - 10:29 am UTC

Last updated: September 10, 2012 - 7:35 pm UTC

Version: 11.2

Viewed 50K+ times! This question is

You Asked

Hello,

I have a requirement that I am dealing with.

I have 2 tables with parent-child relationship. The relationship is zero-to-many.
More specifically, the parent table is called "ServiceRequest" and the child table is called "SR_FAQ". One record in table "ServiceRequest" is related to zero or more records in table "SR_FAQ". The number of records of
SR_FAQ that are related to a record of ServiceRequest vary. It could be none or it could be 3 or 4 or anything.

The table structures in their simplest form are:

CREATE TABLE ServiceRequest (ROW_ID NUMBER, SR_NUM NUMBER, CONSTRAINT SR_PK PRIMARY KEY (ROW_ID) )
CREATE TABLE SR_FAQ (ROW_ID NUMBER, QUESTION VARCHAR2(200 CHAR), ANSWER VARCHAR2(200 CHAR), PAR_SR_ID NUMBER, CONSTRAINT SR_FAQ_PK PRIMARY KEY (ROW_ID), CONSTRAINT SR_FAQ_FK FOREIGN KEY(PAR_SR_ID) REFERENCES ServiceRequest(ROW_ID)

Following are the insert statements:

INSERT INTO ServiceRequest(1,1);
INSERT INTO SR_FAQ (4,'Question1','Answer1',1);
INSERT INTO SR_FAQ (5,'Question2','Answer2',1);

The SR_FAQ table, in essence, is a collection of question/answer records.

I need to create a one-record report that displays the primary key of the parent ServiceRequest record and next to it all the related pairs of "question/answer" from the SR_FAQ table.
For example:
1, Question1, Answer1, Question2, Answer2

where 1 is the primary key of ServiceRequest and next are the related questions/answers of the SR_FAQ table.

It seems like a pivot but I tried using the pivot function with no luck. I all cases, SQL needs to know the exact number of columns that will be used in the SELECT statement.
The problem with my requirement is that the number of records of the SR_FAQ table taht are related to a parent record is random and unknown before-hand.

Does Oracle DB provides an easy way to meet such a requirement? If not, could you please provide me with any alternative way to develop this?

Thank you in advance.

Regards,
Apostolis Giannakidis

and Tom said...

You need to run a query to get the set of columns and use that to develop a query:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238



You might also investigate this method:

http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

but I have not yet played with it.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

fantastic link

Sokrates, May 09, 2012 - 8:32 am UTC

that's fantastic, I never thought DML could invalidate a cursor ( and rollback afterwards make it valid again ), but it seems it can:
(
setup in pivotFun.sql and pivot_distances.sql
on
http://technology.amis.nl/wp-content/uploads/images/antonsPivoting.zip
)

sokrates@hekla[597]> select amsterdam from table( pivot(  'select *  from city_connections' ) );

 AMSTERDAM
----------

        22

        25
        25

5 rows selected.

sokrates@hekla[597]> delete cities where name='AMSTERDAM';

1 row deleted.

sokrates@hekla[597]> select amsterdam from table( pivot(  'select *  from city_connections' ) );
select amsterdam from table( pivot(  'select *  from city_connections' ) )
       *
ERROR at line 1:
ORA-00904: "AMSTERDAM": invalid identifier


sokrates@hekla[597]> rollback;

Rollback complete.

sokrates@hekla[597]> select amsterdam from table( pivot(  'select *  from city_connections' ) );

 AMSTERDAM
----------

        22

        25
        25

5 rows selected.

no cursor was invalidated

Sokrates, May 22, 2012 - 9:11 am UTC

of course no cursor was invalidated, only the same statement didn't compile anymore.


Tom Kyte
May 22, 2012 - 9:47 am UTC

I don't know what you mean?

to be more precise

Sokrates, September 05, 2012 - 3:53 am UTC

I don't know what you mean?

sorry, that's probably because "cursor" can have different meanings.

To be more precise:
I never though that a valid VIEW ( status VALID in DBA_OBJECTS and you can query it without getting an exception ) can be invalidated with purely doing data manipulation and recompiling it, no data definition on any referenced object involved at all.

Furthermore, Oracle does not behave in a consistent, predictable manner ( which I would call a bug ).

See ( files from the above link ):
Setup.
@pivotFun.sql
create table cities
( id    number(5)
, name  varchar2(30)
)
/

create table connections
( from_id number(5)
, to_id   number(5)
, distance number(5)
)
/

insert into cities
( id, name)
values
( 1, 'AMSTERDAM')
/
insert into cities
( id, name)
values
( 2, 'UTRECHT')
/
insert into connections
( from_id , to_id , distance )
values
( 1, 2, 25)
/
commit;
set serverout off


now, we create a view, verify its status and issue a describe and a select on it.

sokrates@11.2 > create view can_be_invalidated_without_ddl as select amsterdam from table( pivot(  'select *  from city_connections' ) );

View created.

sokrates@11.2 > desc can_be_invalidated_without_ddl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AMSTERDAM                                          NUMBER(5)

sokrates@11.2 > select * from can_be_invalidated_without_ddl where rownum = 0;

no rows selected

sokrates@11.2 > select status from user_objects where object_name = 'CAN_BE_INVALIDATED_WITHOUT_DDL';

STATUS
-------
VALID

1 row selected.


( to be continued on my next comment, seems I have reached my maximum of 1000 words )

to be more precise Part 2

Sokrates, September 05, 2012 - 3:55 am UTC

Before I read Anton's method, I thought, this view would stay valid forever and you could select without exception from it when no DDL on a referenced object is done.
But see ( the following is truely a cut-and-paste from a sqlplus session of mine )
sokrates@11.2 > delete cities where name = 'AMSTERDAM';

1 row deleted.

sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;
select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0
                                               *
ERROR at line 1:
ORA-00904: "AMSTERDAM": invalid identifier


sokrates@11.2 > rollback;

Rollback complete.

sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;

no rows selected

sokrates@11.2 > delete cities where name = 'AMSTERDAM';

1 row deleted.

sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;
select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0
                                               *
ERROR at line 1:
ORA-00904: "AMSTERDAM": invalid identifier


sokrates@11.2 > rollback;

Rollback complete.

sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;

no rows selected

sokrates@11.2 > delete cities where name = 'AMSTERDAM';

1 row deleted.

sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;

no rows selected

sokrates@11.2 > commit;

Commit complete.


note that the third execution of the tagged select didn't raise ORA-00904 as expected !

What I also noticed is, that after deleting AMSTERDAM in the cities table, disconnecting and reconnecting, the ORA-00904 didn't occur anymore:

to be more precise Part 3

Sokrates, September 05, 2012 - 3:56 am UTC 

sokrates@11.2 > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

$ sqlplus sokrates

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 5 10:44:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


sokrates@11.2 > set serverout off
sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;

no rows selected


note further, that the status of the VIEW is still VALID, but a recompile will invalidate it

sokrates@11.2 > select status from user_objects where object_name = 'CAN_BE_INVALIDATED_WITHOUT_DDL';

STATUS
-------
VALID

sokrates@11.2 > alter view CAN_BE_INVALIDATED_WITHOUT_DDL compile;

Warning: View altered with compilation errors.

sokrates@11.2 > select status from user_objects where object_name = 'CAN_BE_INVALIDATED_WITHOUT_DDL';

STATUS
-------
INVALID

sokrates@11.2 > select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0;
select /* might behave unpredictably */ * from can_be_invalidated_without_ddl where rownum = 0
                                               *
ERROR at line 1:
ORA-04063: view "SOKRATES.CAN_BE_INVALIDATED_WITHOUT_DDL" has errors

Tom Kyte
September 10, 2012 - 7:35 pm UTC

I concur that this looks like a bug, but one that will probably be tagged with "not feasible to fix"

or one that would be fixed by saying in the future "you cannot create such a view".

This is one time when I'd almost say we have ventured into "magic", I was very surprised at Anton's approach (and impressed, I would not have thought of it...)

disappointed

Sokrates, September 11, 2012 - 4:07 am UTC

... or one that would be fixed by saying in the future "you cannot create such a view".

That's the "easy" way to "fix" it.
Makes me somewhat disappointed.
Sounds a bit like "hey, look, we have implemented this great feature, you can extend the abilities of your system by writing your own data cartridges, but don't expect them to work in a predictable manner in conjunction with our builtins".

Beside that, I don't see any magic here, just good coding.

Not a bug

Anton, September 11, 2012 - 3:01 pm UTC

I don't think this unpredictably behaviour is a bug. The view becomes invalid if Oracle does a hard parse of the sql/view statement and calls the ODCITableDescribe function of the type. In case of a soft parse ODCITableDescribe isn't called and view doensn't become invalid.

By the way, an updated version of the type
CREATE OR REPLACE TYPE PivotImpl as object
(
  ret_type anytype,      -- The return type of the table function
  stmt varchar2(32767),
  fmt  varchar2(32767),
  aggr_function varchar2(32767),
  override_column_names varchar2(1),
  invalidate varchar2(1),
  cur integer,
  static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@', p_invalidate varchar2 := 'Y' )
  return number,
  static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@', p_invalidate varchar2 := 'Y' )
  return number,
  static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@', p_invalidate varchar2 := 'Y' )
  return number,
  member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
  return number,
  member function ODCITableClose( self in PivotImpl )
  return number
)
/



CREATE OR REPLACE TYPE BODY PivotImpl as
  static function ODCITableDescribe( rtype out anytype, p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@', p_invalidate varchar2 := 'Y' )
  return number
  is
    atyp anytype;
    cur integer;
    numcols number;
    desc_tab dbms_sql.desc_tab2;
    rc sys_refcursor;
    t_c2 varchar2(32767);
    t_fmt varchar2(1000);
    t_cnt pls_integer := 0;
  begin
    cur := dbms_sql.open_cursor;
    dbms_sql.parse( cur, p_stmt, dbms_sql.native );
    dbms_sql.describe_columns2( cur, numcols, desc_tab );
    dbms_sql.close_cursor( cur );
--
    anytype.begincreate( dbms_types.typecode_object, atyp );
    for i in 1 .. numcols - 2
    loop
      atyp.addattr( desc_tab( i ).col_name
                  , case desc_tab( i ).col_type
                      when 1   then dbms_types.typecode_varchar2
                      when 2   then dbms_types.typecode_number
                      when 9   then dbms_types.typecode_varchar2
                      when 11  then dbms_types.typecode_varchar2  -- show rowid as varchar2
                      when 12  then dbms_types.typecode_date
                      when 208 then dbms_types.typecode_varchar2  -- show urowid as varchar2
                      when 96  then dbms_types.typecode_char
                      when 180 then dbms_types.typecode_timestamp
                      when 181 then dbms_types.typecode_timestamp_tz
                      when 231 then dbms_types.typecode_timestamp_ltz
                      when 182 then dbms_types.typecode_interval_ym
                      when 183 then dbms_types.typecode_interval_ds
                    end
                  , desc_tab( i ).col_precision
                  , desc_tab( i ).col_scale
                  , case desc_tab( i ).col_type
                      when 11 then 18  -- for rowid col_max_len = 16, and 18 characters are shown
                      else desc_tab( i ).col_max_len
                    end
                  , desc_tab( i ).col_charsetid
                  , desc_tab( i ).col_charsetform
                  );
    end loop;
    if instr( p_fmt, '@p@' ) > 0
    then
      t_fmt := p_fmt;
    else
      t_fmt := '@p@';
    end if;
    open rc for replace( 'select distinct ' || t_fmt || '
                         from( ' || p_stmt || ' )
                         order by ' || t_fmt
                         , '@p@'
                         , '"' || desc_tab( numcols - 1 ).col_name || '"'
                         );
    loop
      fetch rc into t_c2;
      exit when rc%notfound;
      if substr( upper( p_override_column_names ), 1, 1 ) = 'Y'
      then
        t_cnt := t_cnt + 1;
        atyp.addattr( 'COL_' || upper( p_aggr_function ) || '_' || t_cnt
                    , dbms_types.typecode_varchar2
                    , null
                    , null
                    , 4000
                    , desc_tab( numcols ).col_charsetid
                    , desc_tab( numcols ).col_charsetform
                    );
      else
        atyp.addattr( t_c2
                    , case desc_tab( numcols ).col_type
                        when 1   then dbms_types.typecode_varchar2
                        when 2   then dbms_types.typecode_number
                        when 9   then dbms_types.typecode_varchar2
                        when 11  then dbms_types.typecode_varchar2  -- show rowid as varchar2
                        when 12  then dbms_types.typecode_date
                        when 208 then dbms_types.typecode_urowid
                        when 96  then dbms_types.typecode_char
                        when 180 then dbms_types.typecode_timestamp
                        when 181 then dbms_types.typecode_timestamp_tz
                        when 231 then dbms_types.typecode_timestamp_ltz
                        when 182 then dbms_types.typecode_interval_ym
                        when 183 then dbms_types.typecode_interval_ds
                      end
                    , desc_tab( numcols ).col_precision
                    , desc_tab( numcols ).col_scale
                    , case desc_tab( numcols ).col_type
                        when 11 then 18  -- for rowid col_max_len = 16, and 18 characters are shown
                        else desc_tab( numcols ).col_max_len
                      end
                    , desc_tab( numcols ).col_charsetid
                    , desc_tab( numcols ).col_charsetform
                    );
      end if;
    end loop;
    close rc;
    atyp.endcreate;
    anytype.begincreate( dbms_types.typecode_table, rtype );
    rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
    rtype.endcreate();
    return odciconst.success;
  exception
    when others
    then
      anytype.begincreate( dbms_types.typecode_object, atyp );
      atyp.addattr( 'ERROR'
                  , dbms_types.typecode_varchar2
                  , null
                  , null
                  , 4000
                  , null
                  , null
                  );
      atyp.endcreate;
      anytype.begincreate( dbms_types.typecode_table, rtype );
      rtype.SetInfo( null, null, null, null, null, atyp, dbms_types.typecode_object, 0 );
      rtype.endcreate();
      return odciconst.success;
  end;
--
  static function ODCITablePrepare( sctx out PivotImpl, ti in sys.ODCITabFuncInfo, p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@', p_invalidate varchar2 := 'Y' )
  return number
  is
    prec     pls_integer;
    scale    pls_integer;
    len      pls_integer;
    csid     pls_integer;
    csfrm    pls_integer;
    elem_typ anytype;
    aname    varchar2(30);
    tc       pls_integer;
  begin
    tc := ti.RetType.GetAttrElemInfo( 1, prec, scale, len, csid, csfrm, elem_typ, aname );
--
    if instr( p_fmt, '@p@' ) > 0
    then
      sctx := PivotImpl( elem_typ, p_stmt, p_fmt, p_aggr_function, upper( substr( p_override_column_names, 1, 1 ) ), upper( substr( p_invalidate, 1, 1 ) ), null );
    else
      sctx := PivotImpl( elem_typ, p_stmt, '@p@', p_aggr_function, upper( substr( p_override_column_names, 1, 1 ) ), upper( substr( p_invalidate, 1, 1 ) ), null );
    end if;
    return odciconst.success;
  end;
--
  static function ODCITableStart( sctx in out PivotImpl, p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@', p_invalidate varchar2 := 'Y' )
  return number
  is
    cur         integer;
    numcols     number;
    desc_tab    dbms_sql.desc_tab2;
    t_stmt      varchar2(32767);
    t_stmt2     varchar2(32767);
    type_code   pls_integer;
    prec        pls_integer;
    scale       pls_integer;
    len         pls_integer;
    csid        pls_integer;
    csfrm       pls_integer;
    schema_name varchar2(30);
    type_name   varchar2(30);
    version     varchar2(30);
    attr_count  pls_integer;
    attr_type   anytype;
    attr_name   varchar2(100);
    dummy2      integer;
    rc sys_refcursor;
    t_c2 varchar2(32767);
  begin
    cur := dbms_sql.open_cursor;
    dbms_sql.parse( cur, p_stmt, dbms_sql.native );
    dbms_sql.describe_columns2( cur, numcols, desc_tab );
    dbms_sql.close_cursor( cur );
--
    for i in 1 .. numcols - 2
    loop
      t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"' || chr(10);
      t_stmt2 := t_stmt2 || ', null' || chr(10);
    end loop;
--
    type_code := sctx.ret_type.getinfo( prec
                                      , scale
                                      , len
                                      , csid
                                      , csfrm
                                      , schema_name
                                      , type_name
                                      , version
                                      , attr_count
                                      );
    if substr( upper( p_override_column_names ), 1, 1 ) = 'Y'
    then
      open rc for replace( 'select distinct ' || sctx.fmt || '
                           from( ' || p_stmt || ' )
                           order by ' || sctx.fmt
                           , '@p@'
                           , desc_tab( numcols - 1 ).col_name
                           );
      loop
        fetch rc into t_c2;
        exit when rc%notfound;
        t_stmt := t_stmt || replace( ', to_char( ' || sctx.aggr_function || '( decode( ' || sctx.fmt || ', '
                                   , '@p@'
                                   , '"' || desc_tab( numcols - 1 ).col_name || '"'
                                   );
        t_stmt := t_stmt || '''' || t_c2 || ''', "' || desc_tab( numcols ).col_name || '" ) ) )' || chr(10);
        t_stmt2 := t_stmt2 || ', ''' || t_c2 || '''' || chr(10);
      end loop;
    else
      for i in numcols - 1 .. attr_count
      loop
        type_code := sctx.ret_type.getattreleminfo( i
                                                   , prec
                                                   , scale
                                                   , len
                                                   , csid
                                                   , csfrm
                                                   , attr_type
                                                   , attr_name
                                                   );
        t_stmt := t_stmt || replace( ', ' || sctx.aggr_function || '( decode( ' || sctx.fmt || ', '
                                   , '@p@'
                                   , '"' || desc_tab( numcols - 1 ).col_name || '"'
                                   );
        t_stmt := t_stmt || '''' || attr_name || ''', "' || desc_tab( numcols ).col_name || '" ) )' || chr(10);
      end loop;
    end if;
    t_stmt := 'select ' || substr( t_stmt, 2 ) || ' from ( ' || sctx.stmt || ' )';
    t_stmt2 := 'select ' || substr( t_stmt2, 2 ) || ' from dual union all' || chr(10);
    for i in 1 .. numcols - 2
    loop
      if i = 1
      then
        t_stmt := t_stmt || ' group by "' || desc_tab( i ).col_name || '"';
      else
        t_stmt := t_stmt || ', "' || desc_tab( i ).col_name || '"';
      end if;
    end loop;
    t_stmt := t_stmt || ' order by 1 nulls first';
    if substr( upper( p_override_column_names ), 1, 1 ) = 'Y'
    then
      t_stmt := t_stmt2 || t_stmt;
    end if;
dbms_output.put_line( t_stmt );
--
    sctx.cur := dbms_sql.open_cursor;
    dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
    for i in 1 .. attr_count
    loop
      type_code := sctx.ret_type.getattreleminfo( i
                                                 , prec
                                                 , scale
                                                 , len
                                                 , csid
                                                 , csfrm
                                                 , attr_type
                                                 , attr_name
                                                 );
      case type_code
        when dbms_types.typecode_char          then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
        when dbms_types.typecode_varchar2      then dbms_sql.define_column( sctx.cur, i, 'x', 32767 );
        when dbms_types.typecode_number        then dbms_sql.define_column( sctx.cur, i, cast( null as number ) );
        when dbms_types.typecode_date          then dbms_sql.define_column( sctx.cur, i, cast( null as date ) );
        when dbms_types.typecode_urowid        then dbms_sql.define_column( sctx.cur, i, cast( null as urowid ) );
        when dbms_types.typecode_timestamp     then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp ) );
        when dbms_types.typecode_timestamp_tz  then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with time zone ) );
        when dbms_types.typecode_timestamp_ltz then dbms_sql.define_column( sctx.cur, i, cast( null as timestamp with local time zone ) );
        when dbms_types.typecode_interval_ym   then dbms_sql.define_column( sctx.cur, i, cast( null as interval year to month ) );
        when dbms_types.typecode_interval_ds   then dbms_sql.define_column( sctx.cur, i, cast( null as interval day to second ) );
      end case;
    end loop;
    dummy2 := dbms_sql.execute( sctx.cur );
    return odciconst.success;
  exception
    when others
    then
      sctx.cur := dbms_sql.open_cursor;
      dbms_sql.parse( sctx.cur, 'select :msg from dual union all select :err from dual union all select :stm from dual', dbms_sql.native );
      dbms_sql.bind_variable( sctx.cur, 'msg', 'Oops, not a valid query?' );
      dbms_sql.bind_variable( sctx.cur, 'err', dbms_utility.format_error_stack );
      dbms_sql.bind_variable( sctx.cur, 'stm', t_stmt );
      dbms_sql.define_column( sctx.cur, 1, 'x', 32767 );
      dummy2 := dbms_sql.execute( sctx.cur );
      return odciconst.success;
  end;
--
  member function ODCITableFetch( self in out PivotImpl, nrows in number, outset out anydataset )
  return number
  is
    c1_col_type pls_integer;
    type_code   pls_integer;
    prec        pls_integer;
    scale       pls_integer;
    len         pls_integer;
    csid        pls_integer;
    csfrm       pls_integer;
    schema_name varchar2(30);
    type_name   varchar2(30);
    version     varchar2(30);
    attr_count  pls_integer;
    attr_type   anytype;
    attr_name   varchar2(100);
    v1     varchar2(32767);
    n1     number;
    d1     date;
    ur1    urowid;
    ids1   interval day to second;
    iym1   interval year to month;
    ts1    timestamp;
    tstz1  timestamp with time zone;
    tsltz1 timestamp with local time zone;
  begin
    outset := null;
    if nrows < 1
    then
-- is this possible???
      return odciconst.success;
    end if;
--
    if dbms_sql.fetch_rows( self.cur ) = 0
    then
      return odciconst.success;
    end if;
--
    type_code := self.ret_type.getinfo( prec
                                      , scale
                                      , len
                                      , csid
                                      , csfrm
                                      , schema_name
                                      , type_name
                                      , version
                                      , attr_count
                                      );
    anydataset.begincreate( dbms_types.typecode_object, self.ret_type, outset );
    outset.addinstance;
    outset.piecewise();
    for i in 1 .. attr_count
    loop
      type_code := self.ret_type.getattreleminfo( i
                                                 , prec
                                                 , scale
                                                 , len
                                                 , csid
                                                 , csfrm
                                                 , attr_type
                                                 , attr_name
                                                 );
        case type_code
          when dbms_types.typecode_char then
            dbms_sql.column_value( self.cur, i, v1 );
            outset.setchar( v1 );
          when dbms_types.typecode_varchar2 then
            dbms_sql.column_value( self.cur, i, v1 );
            outset.setvarchar2( v1 );
          when dbms_types.typecode_number then
            dbms_sql.column_value( self.cur, i, n1 );
            outset.setnumber( n1 );
          when dbms_types.typecode_date then
            dbms_sql.column_value( self.cur, i, d1 );
            outset.setdate( d1 );
          when dbms_types.typecode_urowid then
            dbms_sql.column_value( self.cur, i, ur1 );
            outset.seturowid( ur1 );
          when dbms_types.typecode_interval_ds then
            dbms_sql.column_value( self.cur, i, ids1 );
            outset.setintervalds( ids1 );
          when dbms_types.typecode_interval_ym then
            dbms_sql.column_value( self.cur, i, iym1 );
            outset.setintervalym( iym1 );
          when dbms_types.typecode_timestamp then
            dbms_sql.column_value( self.cur, i, ts1 );
            outset.settimestamp( ts1 );
          when dbms_types.typecode_timestamp_tz then
            dbms_sql.column_value( self.cur, i, tstz1 );
            outset.settimestamptz( tstz1 );
          when dbms_types.typecode_timestamp_ltz then
            dbms_sql.column_value( self.cur, i, tsltz1 );
            outset.settimestampltz( tsltz1 );
        end case;
    end loop;
    outset.endcreate;
    return odciconst.success;
  end;
--
  member function ODCITableClose( self in PivotImpl )
  return number
  is
    c integer;
    t_id number;
  begin
    c := self.cur;
    dbms_sql.close_cursor( c );
    if self.invalidate = 'Y'
    then
      select object_id
      into t_id
      from user_objects
      where object_name = $$PLSQL_UNIT -- name of your type!
      and object_type = 'TYPE BODY';
 -- invalidating of the type body forces that ODCITableDescribe is executed for every call to the pivot function
 -- and we do need that to make sure that any new columns are picked up (= new values for the pivoting column)
      dbms_utility.invalidate( t_id );
    end if;
    return odciconst.success;
  end;
end;
/

create or replace
function pivot( p_stmt in varchar2, p_aggr_function in varchar2 := 'max', p_override_column_names in varchar2 := 'N', p_fmt in varchar2 := '@p@' )
return anydataset pipelined using PivotImpl;
/


select * from table( pivot( q'~select mod( level, 3 ) + 1 a, 'abc' || mod( level, 5 ) x, mod( level + 4, 5 ) y from dual connect by level <= 50~', 'sum' ) )

select * from table( pivot( q'~select mod( level, 3 ) + 1 a, 'abc' || mod( level, 5 ) x, mod( level + 4, 5 ) y from dual connect by level <= 50~', 'count' ) )

select * from table( pivot( q'~select mod( level, 3 ) + 1 a, 'abc' || mod( level, 5 ) x, mod( level + 4, 5 )/7 y from dual connect by level <= 50~', 'sum', 'Y' ) )

select * from table( pivot( q'~select mod( level, 3 ) + 1 aa, sysdate + mod( level, 5 ) x, mod( level + 4, 5 ) y from dual connect by level <= 50~', 'count', 'n', 'to_char(@p@,''dd-mon-yyyy'')' ) )

Volatile results of the query

QAni, September 17, 2013 - 3:33 pm UTC

Hi Anton,
I have a question for you.
Before that, I used these function, modified few things according to my need in the columns and the query gives the Exact result! just like expected , Magic !

Q. The qipelined query which gives the pivoted result is givving a issue. The query when executed again for different parameters gives previous results itself. kind of storing in the buffer.
the irony is, when I slightly modify the sql under the quotes s/a adding a small space after select or anywhere else gives the right result again.
I dont know why is this hapning. I tried flushing the buffer but it did not work.
Could you please tell me what shud be the issue.
P.S. I overcame the ODCITABLEDESCRIBE error.
Please let me know

There is a better way

bilal, May 17, 2014 - 2:43 am UTC

Hi! If the set of rows to be "pivoted" is "known" before-hand there is a better approach.
Say I have row1, row2, row3.... row100. I would create a just-sql view with the pivot clause. next all I have to do is to create a procedure. with the name of the columns(rows) I want... a query in a my_string ... an OPEN sys_refcursor_variable for my_string and boom the results.
One downside to this might be "performance", haven't benchmark it yet.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.