1:介绍关于行列转换前的通用知识
Pivot查询是这样的，当你想要取出一些如下的数据时：
C1 C2 C3
������������
a1 b1 x1
a1 b1 x2
a1 b1 x3
想按如下格式显示：
C1 C2 C3(1) C3(2) C3(3)
A1 b1 x1 x2 x3
这样就将行转变成了列。
2：
在我们的日常开发中，经常会遇到这样的情况，就是分类汇总，而用一般的SQL写出的程序，数据是向行的方向展开的，但大部分报表的设计是希望向列的方向展开，这样的话，行列转换是非常必要和有用的！
3:通用转换包的实现
首先看一段代码
这段代码是将部门的收入前3名选择出来并用列的形式显示出来。（关于partiton by ,order by,
row_number over请参考相关分析函数的说明
下边是关于通包的包头说明：
create or replace package r_c_pkg
as
type refcursor is ref cursor; --定义一个结果信类型，用来返回结果集
type array is table of varchar2(30);--定义表结构类型。
procedure dbpivot(p_class_code_array in array ,
p_query in varchar2,
p_row_value in array,
p_class_value in array,
p_sum_row in varchar2,
p_cursor in out refcursor);
end ;
��������������
包体：
create or replace package body r_c_pkg
as
procedure dbpivot(p_class_code_array in array ,--存储类编号的值
p_query in varchar2,--必须有一项为CLASS_code,别名也可以
p_row_value in array,--需要做为列的值
p_class_value in array,--需要根据分类取的值
p_sum_row in varchar2,--如果还要在进行SUM的一些值
p_cursor in out refcursor)
as
------------------------------------------------
/*参数示例
p_class_code=> r_c_pkg.array('001','002','005') -其中001,002为需要分列显示的分类值
p_query =>'select a.pdep_code,c.class_code,sum(a.c_pay) as c_pay,sum(a.base_qty) as b_qty,
row_number()over
(partition by a.pdep_code
order by c.class_code nulls last) seq
from user_charge_tbl a,user_tbl c
where a.pdep_code='18' and a.user_id=c.user_id
group by a.pdep_code,c.class_code
'
p_row_value => r_c_pkg.array('pdep_code') 所有查询最前边那个GROUP BY 对像
p_class_value => r_c_pkg.arry('c_pay','b_qty') 是那个CLASS――CODE后边需要显示的值
p_cursor =>r_c_pkg.refcursor 目的是将得到的结果返回出来
p_sum_row=>'sum(base_cost),sum(f_cost)
*/
-------------------------------------------------
v_ncol_count number;
l_query long ;
v_class LONG;
l_row_value varchar2(500);
v_test_string varchar2(500);
begin
l_query:='select'||' ' ;
for j in 1..p_row_value.count -1
loop
l_row_value:=l_row_value||' '||p_row_value(j)||',';
end loop;
l_query:=l_query||l_row_value ;
--------------mxfhhh20060330
--l_query:=rtrim(l_query,',');
--------------mxfhhh20060330
for i in 1..p_class_code_array.count
loop
for k in 1..p_class_value.count-1
loop
--max(decode(class_code,'005',c_pay,0))f005
v_class:=v_class||' '||'max(decode(class_code,'||p_class_code_array(i)||
','||p_class_value(k)||',0))'||
' f'||p_class_code_array(i)||p_class_value(k)||',';
end loop;
end loop;
l_query:=l_query||v_class;
if p_sum_row='' then
l_query:=rtrim(l_query,',')||' from('||p_query||') group by ';
else
l_query:=l_query||p_sum_row||' from('||p_query||') group by ';
end if;
for j in 1..p_row_value.count
loop
l_query:=l_query||p_row_value(j);
v_test_string :=p_row_value(j)||v_test_string;
end loop;
l_query:=rtrim(l_query,',');
execute immediate 'alter session set cursor_sharing=force';
open p_cursor for l_query;
execute immediate 'alter session set cursor_sharing=exact';
---select user_id into l_query from user_tbl;
end;
end;
整个包的目的是动态生成您需要的行列转换内容，并返回结果集.
动态生成的SQL语句如下所示：
select sarea_code,
max(decode(class_code,004,c_pay,0)) f004c_pay,
max(decode(class_code,004,b_qty,0)) f004b_qty,
max(decode(class_code,005,c_pay,0)) f005c_pay,
max(decode(class_code,005,b_qty,0)) f005b_qty,
max(decode(class_code,006,c_pay,0)) f006c_pay,
max(decode(class_code,006,b_qty,0)) f006b_qty,
max(decode(class_code,007,c_pay,0)) f007c_pay,
max(decode(class_code,007,b_qty,0)) f007b_qty,
max(decode(class_code,008,c_pay,0)) f008c_pay,
max(decode(class_code,008,b_qty,0)) f008b_qty,
sum(base_cost),
sum(f_cost)
from
(
select a.sarea_code,c.class_code,sum(a.c_pay) as c_pay,
sum(a.base_qty+a.closs_qty+a.ex_qty+a.ad_qty) as b_qty,sum(a.base_cost) as base_cost,
sum(a.fadjust_cost) as f_cost,
row_number()over
(partition by a.sarea_code
order by c.class_code nulls last) seq
from user_charge_tbl a,user_tbl c
where a.sarea_code>='18001' and a.sarea_code<='18002' and a.user_id=c.user_id
group by a.sarea_code,c.class_code
)
group by sarea_code
以上程序根据工作需要而完成的，希望对那些报表设计有需要的同行有帮助。
参考书：oracle-one-on-one
--
Guyq <guyq@dealeasy.com>
没有评论:
发表评论