之前发过一个帖子,叫行转列的通用过程,
http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html
能满足最基本的需求。但也有一些缺陷,现在对其进行完善
代码
1.使用视图
这里修改了传入参数名,使其更容易理解。继续使用了创建视图这个方法,当然也可以改成用游标传出。
参数:
tabname 需要进行行转列操作的表名;
group_col 查询结果要按某列或某些列分组的字段名;
column_col 要从行转成列的字段;
value_col 需要聚合的值字段;
Aggregate_func 选用的聚合函数,可选,默认为max;
colorder 行转列后列的排序,可选;
roworder 行转列后记录的排序,可选;
when_value_null 若value_col字段的值聚合后为空,则转换成该值,可选;
viewname 创建的视图名称,可选,默认为v_tmp。
举例:
我现在想根据year和month分组,将部门转成列。
这个结果可能不是我们想要的,重新调用过程,使用几个可选参数
进行行转列的也可以是视图
-----------------------------------------------------------
2.稍加修改,使用函数,返回游标。或利用过程里的传出参数
在pl/sql dev中可以在sql窗口执行,查看结果
代码
1.使用视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
create
or
replace
procedure
row_to_col(tabname
in
varchar2,
group_col
in
varchar2,
column_col
in
varchar2,
value_col
in
varchar2,
Aggregate_func
in
varchar2
default
'max'
,
colorder
in
varchar2
default
null
,
roworder
in
varchar2
default
null
,
when_value_null
in
varchar2
default
null
,
viewname
in
varchar2
default
'v_tmp'
)
Authid
Current_User
as
sqlstr varchar2(2000):=
'create or replace view '
||viewname||
' as select '
||group_col||
' '
;
c1 sys_refcursor;
v1 varchar2(100);
begin
open
c1
for
'select distinct '
||column_col||
' from '
||tabname||
case
when
colorder
is
not
null
then
' order by '
||colorder
end
;
loop
fetch
c1
into
v1;
exit
when
c1%notfound;
sqlstr:=sqlstr||chr(10)||
','
||
case
when
when_value_null
is
not
null
then
'nvl('
end
||
Aggregate_func||
'(decode(to_char('
||column_col||
'),'
''
||v1||
''
','
||value_col||
'))'
||
case
when
when_value_null
is
not
null
then
chr(44) ||when_value_null||chr(41)
end
||
'"'
||v1||
'"'
;
end
loop;
close
c1;
sqlstr:=sqlstr||
' from '
||tabname||
' group by '
||group_col||
case
when
roworder
is
not
null
then
' order by '
||roworder
end
;
execute
immediate sqlstr;
end
row_to_col;
|
这里修改了传入参数名,使其更容易理解。继续使用了创建视图这个方法,当然也可以改成用游标传出。
参数:
tabname 需要进行行转列操作的表名;
group_col 查询结果要按某列或某些列分组的字段名;
column_col 要从行转成列的字段;
value_col 需要聚合的值字段;
Aggregate_func 选用的聚合函数,可选,默认为max;
colorder 行转列后列的排序,可选;
roworder 行转列后记录的排序,可选;
when_value_null 若value_col字段的值聚合后为空,则转换成该值,可选;
viewname 创建的视图名称,可选,默认为v_tmp。
举例:
1
2
3
4
5
6
7
8
9
10
11
|
--测试数据
create
table
rowtocol_test
as
select
2009
year
,1
month
,
'部门1'
dept,50000 expenditure
from
dual
union
all
select
2009,2,
'部门1'
,20000
from
dual
union
all
select
2009,2,
'部门1'
,30000
from
dual
union
all
select
2010,1,
'部门1'
,35000
from
dual
union
all
select
2009,2,
'部门2'
,40000
from
dual
union
all
select
2009,3,
'部门2'
,25000
from
dual
union
all
select
2010,2,
'部门3'
,60000
from
dual
union
all
select
2009,2,
'部门3'
,15000
from
dual
union
all
select
2009,2,
'部门3'
,10000
from
dual;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SQL>
select
*
from
rowtocol_test;
YEAR
MONTH
DEPT EXPENDITURE
---------- ---------- ----- -----------
2009 1 部门1 50000
2009 2 部门1 20000
2009 2 部门1 30000
2010 1 部门1 35000
2009 2 部门2 40000
2009 3 部门2 25000
2010 2 部门3 60000
2009 2 部门3 15000
2009 2 部门3 10000
9
rows
selected
SQL>
execute
row_to_col(
'rowtocol_test'
,
'year,month'
,
'dept'
,
'expenditure'
);
PL/SQL
procedure
successfully completed
SQL>
select
*
from
v_tmp;
YEAR
MONTH
部门1 部门3 部门2
---------- ---------- ---------- ---------- ----------
2009 1 50000
2010 1 35000
2009 3 25000
2009 2 30000 15000 40000
2010 2 60000
SQL>
|
这个结果可能不是我们想要的,重新调用过程,使用几个可选参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
execute
row_to_col(
'rowtocol_test'
,
'year,month'
,
'dept'
,
'expenditure'
,Aggregate_func =>
'sum'
,colorder =>
'dept'
,roworder =>
'1,2'
,when_value_null =>
'0'
);
PL/SQL
procedure
successfully completed
SQL>
select
*
from
v_tmp;
YEAR
MONTH
部门1 部门2 部门3
---------- ---------- ---------- ---------- ----------
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
2010 1 35000 0 0
2010 2 0 0 60000
SQL>
|
进行行转列的也可以是视图
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
create
view
view_rowtocol
as
select
*
from
rowtocol_test
where
year
=2009;
View
created
SQL>
execute
row_to_col(
'view_rowtocol'
,
'year,month'
,
'dept'
,
'expenditure'
,Aggregate_func =>
'sum'
,colorder =>
'dept'
,roworder =>
'1,2'
,when_value_null =>
'0'
);
PL/SQL
procedure
successfully completed
SQL>
select
*
from
v_tmp;
YEAR
MONTH
部门1 部门2 部门3
---------- ---------- ---------- ---------- ----------
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
SQL>
|
-----------------------------------------------------------
2.稍加修改,使用函数,返回游标。或利用过程里的传出参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
create
or
replace
function
row_to_col_func(tabname
in
varchar2,
group_col
in
varchar2,
column_col
in
varchar2,
value_col
in
varchar2,
Aggregate_func
in
varchar2
default
'max'
,
colorder
in
varchar2
default
null
,
roworder
in
varchar2
default
null
,
when_value_null
in
varchar2
default
null
)
return
sys_refcursor
Authid
Current_User
as
sqlstr varchar2(2000):=
'select '
||group_col||
' '
;
c1 sys_refcursor;
v1 varchar2(100);
cur sys_refcursor;
begin
open
c1
for
'select distinct '
||column_col||
' from '
||tabname||
case
when
colorder
is
not
null
then
' order by '
||colorder
end
;
loop
fetch
c1
into
v1;
exit
when
c1%notfound;
sqlstr:=sqlstr||chr(10)||
','
||
case
when
when_value_null
is
not
null
then
'nvl('
end
||
Aggregate_func||
'(decode(to_char('
||column_col||
'),'
''
||v1||
''
','
||value_col||
'))'
||
case
when
when_value_null
is
not
null
then
chr(44) ||when_value_null||chr(41)
end
||
'"'
||v1||
'"'
;
end
loop;
close
c1;
open
cur
for
sqlstr||
' from '
||tabname||
' group by '
||group_col||
case
when
roworder
is
not
null
then
' order by '
||roworder
end
;
return
cur;
end
row_to_col_func;
|
在pl/sql dev中可以在sql窗口执行,查看结果
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select
row_to_col_func(
'rowtocol_test'
,
'year,month'
,
'dept'
,
'expenditure'
,Aggregate_func =>
'sum'
,colorder =>
'dept'
,roworder =>
'1,2'
,when_value_null =>
'0'
)
from
dual;
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<
Cursor
>
YEAR
MONTH
部门1 部门2 部门3
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
2010 1 35000 0 0
2010 2 0 0 60000
|