原来
xml转换:
select * from
(select cust_coding,delivery_amt,mth from rpt_sales_quota_month where yearly=2019 and brand_coding='0')
pivot xml(sum(delivery_amt) for mth in (any))
使用存储过程解析xml:
create or replace procedure xml_pivot(pi_table_name varchar2,
pi_column_name varchar2
pi_create_view varchar2,
)
return varchar2 as
v_column nvarchar2(50);
v_count number := 0;
v_i number;
v_parent_node nvarchar2(4000);
v_child_node nvarchar2(4000);
v_over boolean := false;
v_tmp nvarchar2(50);
v_existsnode number;
v_sql clob;
v_name varchar2(30);
v_name_xml xmltype;
begin
v_sql := 'select x.* from ' || pi_table_name ||
' a, xmltable(''/PivotSet'' passing a.' ||
pi_column_name || ' columns ';
v_parent_node := '/PivotSet';
v_child_node := 'item[1]/column[2]';
v_i := 1;
execute immediate 'select ' || pi_column_name || ' from ' ||
pi_table_name || ' where rownum=1'
into v_name_xml;
select existsnode(v_name_xml,
'/PivotSet/item[' || to_char(v_i) || ']/column[1]')
into v_existsnode
from dual;
while v_existsnode = 1 loop
dbms_output.put_line( 'select substr(extractvalue(' || pi_column_name ||
', ''/PivotSet/item[' || to_char(v_i) || ']/column[1]''),1,30)
from ' || '(select * from (' || pi_table_name || ' ) where rownum=1 ) ) x');
execute immediate 'select substr(extractvalue(' || pi_column_name ||
', ''/PivotSet/item[' || to_char(v_i) || ']/column[1]''),1,30)
from ' || '(select * from ' || pi_table_name || ' where rownum=1 ) x'
into v_name;
v_sql := v_sql || '"' || v_name || '" varchar2(30) path ''item[' ||
to_char(v_i) || ']/column[2]'',';
v_i := v_i + 1;
select existsnode(v_name_xml,
'/PivotSet/item[' || to_char(v_i) || ']/column[1]')
into v_existsnode
from dual;
end loop;
v_sql := trim(',' from v_sql) || ') x';
v_sql := replace(v_sql,'x.*','a.*,x.*');
execute immediate 'create or replace view ' || pi_create_view || ' as ' || v_sql;
/*select count(1)
into v_count
from user_tab_columns
where table_name = upper(pi_create_table);
if v_count = 0 then
dbms_output.put_line(v_sql);
execute immediate 'create or replace view ' || pi_create_view || ' as ' || v_sql;
end if;*/
end;
调用:
call xml_pivot
('(select * from (select cust_coding,delivery_amt,mth
from rpt_sales_quota_month where yearly=2019 and brand_coding=''0'')
pivot xml(sum(delivery_amt) for mth in (any) )
)','mth_xml') ;