Oracle 10G no PIVOT function What to do, write yourself a soon have

As we all know, the output structure of static SQL must also be static. For classic row-to-column problems, if the number of rows is variable, the standard answer is to use dynamic SQL, which is a pivot with XML results in 11G.

But Oracle 10G does not have a PIVOT function to do, write one of their own soon. Direct point on the code.

CREATE OR REPLACE
Type Pivotimpl_shx As Object
(
Ret_type anytype,-The return type of the table function stmt varchar2 (32767),
Fmt varchar2 (32767),
cur integer,
static function Odcitabledescribe (Rtype out Anytyp E, p_stmt in Varchar2, p_fmt in varchar2: = ' upper (@[email protected]) ', dummy in number: = 0)
Return NUMBER,
static function Odcitableprepare (Sctx out pivotimpl_shx, ti in sys. Odcitabfuncinfo, p_stmt in Varchar2, p_fmt in varchar2: = ' upper (@[email protected]) ', dummy in number: = 0)
RET Urn number,
static function Odcitablestart (Sctx in Out Pivotimpl_shx, p_stmt in Varchar2, p_fmt in varchar2: = ' Upper (@[email protected]) ', dummy in number: = 0)
Return number,
member function Odcitablefetch Timpl_shx, nrows in number, outset out Anydataset)
return number,
member function odcitableclose (self in Pivotim PL_SHX)
Return number
)
/

Create or replace type body pivotimpl_shx as
static function Odcitabledescribe (Rtype out Anytype, p_stmt in Varchar2, p_fmt in varchar2: = ' upper (@[email protected]) ' , dummy in number)
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);
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 one and then DBMS_TYPES.TYPECODE_VARCHAR2-show rowID as Varchar2
When Dbms_types.typecode_date
When 208 then DBMS_TYPES.TYPECODE_VARCHAR2--show Urowid as Varchar2
When Dbms_types.typecode_char
When the Dbms_types.typecode_timestamp then
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 one then all--for rowid Col_max_len = +, and 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, ' @[email protected] ') > 0
Then
T_FMT: = p_fmt;
Else
T_FMT: = ' @[email protected] ';
End If;
Open RC for replace (' select distinct ' | | t_fmt | | ‘
From (' | | | p_stmt | | ‘ )
Order BY ' | | T_fmt
, ' @[email protected] '
, Desc_tab (numcols-1). col_name
);
Loop
Fetch RC into T_C2;
Exit when Rc%notfound;
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 one and then DBMS_TYPES.TYPECODE_VARCHAR2-show rowID as Varchar2
When Dbms_types.typecode_date
When 208 then Dbms_types.typecode_urowid
When Dbms_types.typecode_char
When the Dbms_types.typecode_timestamp then
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 one then all--for rowid Col_max_len = +, and characters are shown
else Desc_tab (numcols). Col_max_len
End
, Desc_tab (numcols). Col_charsetid
, Desc_tab (numcols). Col_charsetform
);
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
return odciconst.error;
End
--
static function Odcitableprepare (Sctx out pivotimpl_shx, ti in sys. Odcitabfuncinfo, p_stmt in Varchar2, p_fmt in varchar2: = ' upper (@[email protected]) ', dummy in number
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, ' @[email protected] ') > 0
Then
Sctx: = Pivotimpl_shx (Elem_typ, p_stmt, p_fmt, NULL);
Else
Sctx: = Pivotimpl_shx (Elem_typ, p_stmt, ' @[email protected] ', null);
End If;
return odciconst.success;
End
--
static function Odcitablestart (Sctx in Out Pivotimpl_shx, p_stmt in Varchar2, p_fmt in varchar2: = ' Upper (@[email protect Ed]) ', dummy in number
return number
Is
Cur integer;
Numcols number;
Desc_tab dbms_sql.desc_tab2;
T_stmt 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;
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 | | ‘"‘;
End Loop;
--
Type_code: = Sctx.ret_type.getinfo (prec
, scale
Len
, CSID
, csfrm
, schema_name
, type_name
, version
, Attr_count
);
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 (', Max (decode (' | | | sctx.fmt | | ', ' ' | | Attr_name | | "', ' | | Desc_tab (numcols). Col_name | | ‘ ) )‘
, ' @[email protected] '
, Desc_tab (numcols-1). col_name
);
End Loop;
T_STMT: = ' SELECT ' | | SUBSTR (t_stmt, 2) | | ' From (' | | | sctx.stmt | | ‘ )‘;
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;
--
--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 and Dbms_sql.define_column (Sctx.cur, I, cast (null as timestamp with time zone) ;
When Dbms_types.typecode_timestamp_ltz and Dbms_sql.define_column (Sctx.cur, I, cast (null as timestamp with local time Z one));
When Dbms_types.typecode_interval_ym and Dbms_sql.define_column (Sctx.cur, I, cast (null as interval year to month));
When Dbms_types.typecode_interval_ds and 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;
End
--
member function Odcitablefetch (self in Out pivotimpl_shx, 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;
--
--dbms_output.put_line (' fetch ');
If Dbms_sql.fetch_rows (self.cur) = 0
Then
return odciconst.success;
End If;
--
--dbms_output.put_line (' done ');
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
);
--dbms_output.put_line (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 and then
Dbms_sql.column_value (Self.cur, I, IYM1);
Outset.setintervalym (IYM1);
when Dbms_types.typecode_timestamp and then
Dbms_sql.column_value (Self.cur, I, ts1);
Outset.settimestamp (ts1 );
when Dbms_types.typecode_timestamp_tz and then
Dbms_sql.column_value (Self.cur, I, TSTZ1);
OUTSET.SETTIMESTAMPTZ (TSTZ1);
when Dbms_types.typecode_timestamp_ltz and 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_shx)
return number
is
C integer;
begin
C : = Self.cur;
Dbms_sql.close_cursor (c);
return odciconst.success;
End;
End;
/

--wrapping a layer of plsql functions outside:
Create or replace
function Pivot_shx (p_stmt in Varchar2, p_fmt in varchar2: = ' upper (@[email protected]) ', dummy in number: = 0)
Return anydataset pipelined using Pivotimpl_shx;
/

Test examples

With Tmp_tab as (
Select Da.duty_user Ucode, u.user_name as uname,
Case time Da.morning>0 Then ' early ': ' | | To_char (da.morning) end as Morningnum,
Case is da.noon>0 then ' in ': ' | | To_char (Da.noon) end as Noonnum,
case where da.night>0 then ' late ': ' | | To_char (da.night) end as Nightnum,
case where da.other>0 then ' its ': ' | | To_char (da.other) end as Othernum,
case where da.zheng1>0 then ' positive 1 ': ' | | To_char (da.zheng1) end as Zheng1num,
case where da.zheng2>0 then ' positive 2 ': ' | | To_char (da.zheng2) End as Zheng2num
From (
Select Du.duty_user,
SUM (case time du.duty_schedule like ' early% ' then 1 end) as morning,
SUM (case if du.duty_schedule like ' late% ' then 1 end) as night,
SUM (case if du.duty_schedule like ' in% ' then 1 end) as noon,
SUM (case when du.duty_schedule= ' others ' then 1 end) as other,
SUM (case when du.duty_schedule= ' is 1 ' then 1 end) as Zheng1,
SUM (case when du.duty_schedule= ' positive 2 ' then 1 end) as Zheng2
From Crm_duty du where du.is_mark= ' 1 ' and du.duty_user like ' A% '
and Du.duty_time>=to_date (' 2017-06-01 00:00:00 ', ' Yyyy-mm-dd hh24 ': "Mi": "SS ')
and Du.duty_time<=to_date (' 2017-06-20 23:59:59 ', ' Yyyy-mm-dd hh24 ': "Mi": "SS ')
GROUP BY Du.duty_user

) Da,crm_user U
where Da.duty_user=u.user_code
)
Select t2.*, t.*
From table (Pivot_shx (' Select
Duty_user, To_char (Duty_time, "Yyyy-mm-dd") as Dtime, duty_schedule from Crm_duty D
where d.is_mark= ' 1 '
and D.duty_time>=to_date ("2017-06-01 00:00:00", "Yyyy-mm-dd hh24": "Mi": "SS")
and D.duty_time<=to_date (' 2017-06-20 23:59:59 ', ' yyyy-mm-dd hh24 ': ' mi ': ' ss ') ') T, Tmp_tab T2
where T.duty_user=t2.ucode

Oracle 10G no PIVOT function What to do, write yourself a soon have