pivot

先上语法规范：
SELECT ….
FROM
PIVOT
(
aggregate-function()
FOR IN (, ,…, )
) AS
WHERE …..
通过一个例子说明其用法：
select * from (select salary, department_id from employee)
pivot(sum(salary) as sum_sal for(department_id) in (10,20,30));
10_sum_sal 20_sum_sal 30_sum_sal
370000 155000 370000

pivot功能在目前的oracle版本中不支持subquery的用法，所以要动态生成栏目的话就必须用 function 或者是procedure 用循环把字符串生成然后再执行。 游标 或者 临时表都可以用。

方法1、可以使用pivot xml
select * from (
select times_purchased as “Puchase Frequency”, state_code
from customers t
)
pivot xml
(
count(state_code)
for state_code in (select state_code from preferred_states)
)
order by 1

for state_code in (select distinct state_code from
customers)子查询必须返回不同的值，否则查询将失败。这就是我们指定上述 DISTINCT 子句的原因。

wm_concat在oracle11g中换成了listagg

LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

create or replace procedure ljz_pivot_xml_sp(pi_table_name varchar2,
pi_column_name varchar2,
pi_create_table 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
execute immediate ‘select substr(extractvalue(’ || pi_column_name ||
‘, ”/PivotSet/item[’ || to_char(v_i) || ‘]/column[1]”),1,30)
from ’ || pi_table_name || ’ 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’;
commit;
select count(1)
into v_count
from user_tab_columns
where table_name = upper(pi_create_table);
if v_count = 0 then
execute immediate ‘create table ’ || pi_create_table || ’ as ’ || v_sql;
end if;
end;
调用方法：

1
2
3
4
5
begin
ljz_pivot_xml_sp(‘(select * from (select deptno,sal from emp) pivot xml(sum(sal) for deptno in(any)))’,
‘deptno_xml’,
‘ljz_pivot_tmp’);
end;