3

Matrix Reports in APEX

Recently I saw a question on LinkedIn asking how to create Matrix style reports in APEX and found the discussion quite interesting as there were a mix of answers but nothing really concrete on how you could create one. So I thought I’d shed some light on a technique I have been using quite successfully for a number of years, with the most recent implementation just a few weeks ago.

About 4 years ago I came across a couple of posts from Anton Scheffer and Lucas Jellema from AMIS which described the use of a custom type which allowed you to pivot the last 2 columns of your query and turn it into a matrix style report. The great thing was that it is quite straight forward to use in APEX as we can use generic columns for our classic report using the option “Use Generic Column Names (parse query at runtime only)” e.g.

and for the report columns we have the option of either using “Column Names” or “PLSQL” e.g.

Here is the link to download the PIVOTIMPL function code written by Anton. I take absolutely no credit for this amazing bit of PLSQL code. All that I have done is write a simple wrapper around generating the SQL statement, replacing any substitutions, and replacing binds with the “V” function. I am considering updating Anton’s code in the future to accept binds that reference APEX session state items to boost query performance as on large data sets there can be significant degrade on performance using the “V” function.

To define your Classic report you can use a report type of “SQL Query (PLSQL function body returning SQL query)”

with a PLSQL function body returning a SQL query, similar to the following:

RETURN pd_matrix_reports.generate_matrix_query
      ( p_query_stmt => 'SELECT dept_directorate_id'||
                        ',      dept_directorate'||
                        ',      region_id'||
                        ',      region'||
                        ',      manager_id'||
                        ',      manager_name'||
                        ',      period_cal_year'||
                        ',      period_cal_year||'' ''||lpad(period_no,2,''0'') period'||
                        ',      end_year_count_link end_year_count '||
                        'FROM   pd_pdp_period_manager_vw '||
                        'WHERE  (dept_directorate_id =:P54_DEPT_DIRECTORATE_ID OR nvl(:P54_DEPT_DIRECTORATE_ID,''ALL'') = ''ALL'') '||
                        'AND    (region_id =:P54_REGION_ID OR nvl(:P54_REGION_ID,''ALL'') = ''ALL'') '||
                        'AND    period_cal_year = nvl(''&P54_PERIOD_YEAR.'',period_cal_year) '||
                        'AND ( '||
                               'instr(upper(nvl(manager_name,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(manager_name,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(region,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(region,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(dept_directorate,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(dept_directorate,''ALL'')))) > 0 '||
                             ')'
       , p_parse_query => FALSE
       );

and if you need to further filter the actual result set produced by the pivot operation (this is the actual data returned from the table cast so your pivoted data will now be in columns) you can concatenate the resulting string with an additional where clause e.g.

RETURN pd_matrix_reports.generate_matrix_query
      ( p_query_stmt => 'SELECT dept_directorate_id'||
                        ',      dept_directorate'||
                        ',      region_id'||
                        ',      region'||
                        ',      manager_id'||
                        ',      manager_name'||
                        ',      period_cal_year'||
                        ',      period_cal_year||'' ''||lpad(period_no,2,''0'') period'||
                        ',      end_year_count end_year_count '||
                        'FROM   pd_pdp_period_manager_vw '||
                        'WHERE  (dept_directorate_id =:P54_DEPT_DIRECTORATE_ID OR nvl(:P54_DEPT_DIRECTORATE_ID,''ALL'') = ''ALL'') '||
                        'AND    (region_id =:P54_REGION_ID OR nvl(:P54_REGION_ID,''ALL'') = ''ALL'') '||
                        'AND    period_cal_year = nvl(''&P54_PERIOD_YEAR.'',period_cal_year) '||
                        'AND ( '||
                               'instr(upper(nvl(manager_name,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(manager_name,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(region,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(region,''ALL'')))) > 0 or '||
                               'instr(upper(nvl(dept_directorate,''ALL'')),upper(nvl(:P54_REPORT_SEARCH,nvl(dept_directorate,''ALL'')))) > 0 '||
                             ')'
       , p_parse_query => FALSE
       )||
       ' AND EXISTS '||
                '( SELECT NULL '||
                  'FROM   pd_access_vw '||
                  'WHERE  dept_directorate_id = v.dept_directorate_id '||
                  'AND    region_id = v.region_id '||
                ')'||
       ' AND "2011/12 08" > 0';

In the above we can use a column filter on an actual data value e.g. “2011/12 08″ since our second last column has been transposed into columns thanks to Anton’s magical code. I say magical because it’s not the easiest piece of code to understand, but if you’re interested you can find more here’s the 11.2 documentation reference.

Here’s an example screenshot of 3 reports which use this technique to pivot 13 calendar periods in a year…

I can’t thank Anton, Lucas, and AMIS enough for documenting and sharing this code as it’s been a life and time saver in lots of past situations, so I hope you find it as useful as I have within APEX. The only thing you may want to keep your eye on is the dynamic creation of TYPES within your application schema which the code seems to be creating e.g.

  1. Hi!

    I really like the anytype solution, it’s very powerful but I think it does a bit too much in an APEX context. This thread (https://forums.oracle.com/forums/thread.jspa?threadID=697760) describes a different implementation, a function that uses dbms_sql to just build a sql statement and lets a reports region do the rest. I used an adapted version in the past, worked fine.

    Btw, there seems to be an issue with

    http://www.theapexfreelancer.com/apex/wwv_flow.show?p_flow_id=APEXFREELANCER&p_flow_step_id=ABOUT&p_instance=0&p_request=APPLICATION_PROCESS=ccpublic&x01=PIVOTIMPL&x02=http%3A%2F%2Fwww%2Etheapexfreelancer%2Ecom%2Fc%2Fpivotimpl%2Esql

    There is a newline between
    Set-Cookie:WWV_PUBLIC_SESSION_708=27534212605702
    and
    Location: http://www.theapexfreelancer.com/c/pivotimpl.sql
    therefore the page displays the Location: header in the page on the 1st request. It shows pivotimpl.sql after a refresh. APEX wants to set the cookie, because it does not yet exist when I click on the download link from http://blog.theapexfreelancer.com/2011/11/matrix-reports-in-apex/

    I think that could be fixed by calling “htp.init;” at the beginning of your ccpublic process.

    Regards,
    Christian

    • mnolan says:

      Thanks Chris, I hadn’t come across that before so I will definitely take a closer look. Thanks for also pointing out the issue, I’ve just made the change so hopefully the issue is resolved.

  2. Anton says:

    Nice!
    An updated version of the Pivot code:
    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(32767),
    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, p_override_column_names, upper( substr( p_invalidate, 1, 1 ) ), null );
    else
    sctx := PivotImpl( elem_typ, p_stmt, ‘@p@’, p_aggr_function, p_override_column_names, 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
    t_stmt := q’~select ‘Oops, not a valid query?’ from dual~’;
    sctx.cur := dbms_sql.open_cursor;
    dbms_sql.parse( sctx.cur, t_stmt, dbms_sql.native );
    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 = 'PIVOTIMPL' — 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'')' ) )