CREATE OR REPLACE FUNCTION ROW_TO_COL_FUNC (tabname in varchar2,
v_xmbh in varchar2,
v_dwbh in varchar2,
v_lrsj_begin in varchar2 default to_char( Trunc(sysdate,'month'),'yyyy-mm-dd'),
v_lrsj_end in varchar2 default to_char(sysdate,'yyyy-mm-dd')
)return varchar2
Authid Current_User
as
sqlstr varchar2(2000):='select AAB034,AA011,AA001,AAB001,';
ub sys_refcursor;
tab varchar2(100);
vardate varchar2(20):='yyyy-mm-dd';
begin
open ub for 'select distinct AA014 from '||tabname||' where AA011='''||v_xmbh||''' and AAB001= '''||v_dwbh||'''';
loop
fetch ub into tab;
exit when ub%notfound;
sqlstr:=sqlstr||',max(case when AA014='''||tab||''' then AA015 else '''||''' end) '||tab;
end loop ;
close ub;
sqlstr :=sqlstr||',AAE216,AAE218,AA013,AA016 from '||tabname||' where to_char(AAE216,'''||vardate||''') between '''||v_lrsj_begin||''' and '''||v_lrsj_end||''' and AA011='''||v_xmbh||''' and AAB001='''||v_dwbh||''' group by AAB034,AA011,AA001,AAB001,AAE216,AAE218,AA013,AA016';
return sqlstr;
end row_to_col_func;
CREATE TABLE `c_wssb_zz` (
`aa011` varchar(10) default NULL,
`aa001` varchar(20) default NULL,
`aa014` varchar(20) default NULL,
`aa015` varchar(200) default NULL,
`aab001` varchar(20) default NULL,
`aa010` varchar(30) default NULL,
`aae216` date default NULL,
`aa013` varchar(3) default NULL,
`aa016` varchar(10) default NULL,
`aae218` date default NULL,
`aab034` varchar(20) default NULL,
`aab901` varchar(255) default NULL
)
