declare
sqlstr varchar2(8000) := '';
begin
select WM_CONCAT(''''||acct_period||'''') into sqlstr
from (
SELECT acct_period FROM a5_mobile_account t where acct_period>=201703 GROUP BY acct_period
) t;
sqlstr:='SELECT * FROM (SELECT acct_period,credit FROM a5_mobile_account )
PIVOT (
SUM(credit) FOR acct_period
IN ('||sqlstr||')
) ORDER BY 1';
execute immediate sqlstr;
sqlstr := 'CREATE OR REPLACE VIEW tmp_result AS '|| sqlstr;
execute immediate sqlstr;
end;
select * from tmp_result
sqlstr varchar2(8000) := '';
begin
select WM_CONCAT(''''||acct_period||'''') into sqlstr
from (
SELECT acct_period FROM a5_mobile_account t where acct_period>=201703 GROUP BY acct_period
) t;
sqlstr:='SELECT * FROM (SELECT acct_period,credit FROM a5_mobile_account )
PIVOT (
SUM(credit) FOR acct_period
IN ('||sqlstr||')
) ORDER BY 1';
execute immediate sqlstr;
sqlstr := 'CREATE OR REPLACE VIEW tmp_result AS '|| sqlstr;
execute immediate sqlstr;
end;
select * from tmp_result