1.使用视图
SQL code
-
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。
举例:
SQL code
-
--
测试数据
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;
我现在想根据year和month分组,将部门转成列。
SQL code
-
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
>
这个结果可能不是我们想要的,重新调用过程,使用几个可选参数
SQL code
-
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
>
进行行转列的也可以是视图
SQL code
-
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.稍加修改,使用函数,返回游标。或利用过程里的传出参数
SQL code
-
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窗口执行,查看结果
SQL code
-
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
| | | |