EXCEL的透视表是非常棒的一个功能，对于竖表转横表比较有用，我个人认为是除了那些公式计算以外EXCEL最吸引人的功能了。但是EXCEL有着65535行的限制，对于我们这些经常要为经营分析取数、做报表的人显然是远远不够的。Oracle中当然可以实现表的pivot，在Expert One on One Oracle中讲分析函数的一章中也有一个例子讲解如何Pivot，但是Tom的pivot是类似于完全的转换，将几列全部打散（具体请到asktom.oracle.com搜索“pivot”或者参考上述书籍）。而我们需要的是一个Oracle的透视表。也就是如下的效果：
1
CREATE OR REPLACE PACKAGE pkg_pivot
2 AS
3
/******************************************************************************
4
NAME: pkg_pivot
5 PURPOSE:
6
7 REVISIONS:
8 Ver Date Author Description
9 --------- ---------- --------------- ------------------------------------
10 1.0 2005-12-21 ERN 1. 创建包
11 2.0 2005-12-22 ERN 2. 增加pivot_long过程，处理超过200列
12 情况，但仍存在限制
13
******************************************************************************/
14 TYPE refcursor IS REF CURSOR;
15 TYPE ARRAY IS TABLE OF VARCHAR2(30) index by binary_integer;
16 PROCEDURE pivot(p_tablename varchar2,
17 p_anchor varchar2,
18 p_pivot varchar2,
19 p_value varchar2,
20 p_cursor OUT refcursor);
21 PROCEDURE pivot_long(p_tablename varchar2,
22 p_anchor varchar2,
23 p_pivot varchar2,
24 p_value varchar2);
25END;
26/
27create or replace package body pkg_pivot as
28 procedure pivot(p_tablename varchar2, --表名，也可以输入查询
29 p_anchor varchar2, --不变的列名，对于多个列可以用逗号分隔
30 p_pivot varchar2, --将取值转换成列的列名
31 p_value varchar2, --填充的值字段
32 p_cursor out refcursor --返回结果集
33 ) as
34 /******************************************************************************
35 NAME: pivot
36 PURPOSE: 竖表转横表，此过程返回结果集，仅处理组合后32767字节以下的语句。
37 即大约可以处理200列的转换。
38
39 REVISIONS:
40 Ver Date Author Description
41 --------- ---------- --------------- ------------------------------------
42 1.0 2005-12-21 ERN 1. 创建
43
44 ******************************************************************************/
45 ar_col array; --存放转换后的列名
46 n_cnt number;
47 l_query varchar2(32766); --最终的执行语句
48 l_query_pivot varchar2(200); --统计p_pivot转换后的列数与取值
49 begin
50 n_cnt := 0;
51 l_query := 'select ' || p_anchor || ',';
52 l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||
53 p_tablename;
54 open p_cursor for l_query_pivot;
55
56 loop
57 exit when p_cursor%NOTFOUND;
58 n_cnt := n_cnt + 1;
59 fetch p_cursor
60 into ar_col(n_cnt);
61 end loop;
62 n_cnt := n_cnt - 1;
63 close p_cursor;
64
65 for i in 1 .. n_cnt - 1 loop
66 l_query := l_query || 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';
67 end loop;
68
69 l_query := l_query || 'max(val' || to_char(n_cnt) || ') "' ||
70 ar_col(n_cnt) || '" ';
71 l_query := l_query || 'from (select ';
72
73 l_query := l_query || p_anchor || ', ';
74
75 for i in 1 .. n_cnt - 1 loop
76 l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) ||
77 ',rn, null) rn' || to_char(i) || ',';
78 l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) || ',' ||
79 p_value || ',null) val' || to_char(i) || ',';
80
81 end loop;
82 l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) ||
83 ', rn, null) rn' || to_char(n_cnt) || ',';
84 l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) || ',' ||
85 p_value || ',null) val' || to_char(n_cnt) || ' ';
86
87 l_query := l_query || 'from (select ';
88
89 l_query := l_query || p_anchor || ', ';
90
91 l_query := l_query || p_pivot || ', ' || p_value ||
92 ', row_number() over(partition by ';
93
94 l_query := l_query || p_anchor || ', ';
95
96 l_query := l_query || p_pivot || ' ';
97 l_query := l_query || 'order by ' || p_value || ') rn from ' ||
98 p_tablename || ') t) t group by ';
99
100 l_query := l_query || p_anchor || ' ';
101
102 execute immediate 'alter session set cursor_sharing=force';
103
104 open p_cursor for l_query;
105
106 execute immediate 'alter session set cursor_sharing=exact';
107
108 end;
109 procedure pivot_long(p_tablename varchar2,
110 p_anchor varchar2,
111 p_pivot varchar2,
112 p_value varchar2) as
113 /******************************************************************************
114 NAME: pivot_long
115 PURPOSE: 竖表转横表，此过程建立_ext表，用于处理组合后查询语句超过32676
116 字节的情况；对超长语句必须用dbms_sql进行解析、执行。但此处还受
117 聚集过程中限制，对于过长的分组聚集语句会报ORA-01467，内部机制是
118 受块大小影响，除非使用16K的大块，否则无法绕过此问题。
119
120 REVISIONS:
121 Ver Date Author Description
122 --------- ---------- --------------- ------------------------------------
123 1.0 2005-12-22 ERN 1. 创建
124
125 ******************************************************************************/
126 ar_col array;
127 n_cnt number;
128 l_tmp varchar2(3256);
129 p_cursor refcursor;
130 l_query dbms_sql.varchar2s;
131 n_ind number;
132 n_left number;
133 l_query_pivot varchar2(3200);
134 l_cursor integer default dbms_sql.open_cursor;
135 n_result number;
136 begin
137 -- l_query(1) :='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
138 n_cnt := 0;
139
140 l_query(1) := 'create table ' ||
141 substr(p_tablename,
142 instr(p_tablename, '.') + 1,
143 instr(p_tablename, 'where') -
144 instr(p_tablename, '.') - 2) || 'ext as ';
145
146 n_ind := 2;
147 l_query(n_ind) := 'select ' || p_anchor || ',';
148 l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||
149 p_tablename;
150 open p_cursor for l_query_pivot;
151
152 loop
153 exit when p_cursor%NOTFOUND;
154 n_cnt := n_cnt + 1;
155 fetch p_cursor
156 into ar_col(n_cnt);
157 end loop;
158 n_cnt := n_cnt - 1;
159 close p_cursor;
160
161
162 n_ind := n_ind + 1;
163 l_query(n_ind) := '';
164 for i in 1 .. n_cnt - 1 loop
165 l_tmp := 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';
166-- if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 255 then
167 if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then
168-- gl dbms_output.put_line(l_query(n_ind));
169-- gl dbms_output.put_line(lengthb(l_query(n_ind)));
170 l_query(n_ind) := l_query(n_ind) || l_tmp;
171 else
172 n_left := 100 - length(l_query(n_ind));
173 l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);
174 n_ind := n_ind + 1;
175 l_query(n_ind) := substr(l_tmp, n_left + 1);
176 end if;
177 end loop;
178 n_ind := n_ind + 1;
179 l_query(n_ind) := '';
180
181 l_query(n_ind) := 'max(val' || to_char(n_cnt) || ') "' || ar_col(n_cnt) || '" ';
182 l_query(n_ind) := l_query(n_ind) || 'from (select ';
183
184 l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
185 n_ind := n_ind + 1;
186 l_query(n_ind) := '';
187 for i in 1 .. n_cnt - 1 loop
188 l_tmp := 'decode(' || p_pivot || ',''' || ar_col(i) || ''',rn, null) rn' ||
189 to_char(i) || ',';
190 l_tmp := l_tmp || 'decode(' || p_pivot || ',''' || ar_col(i) || ''',' ||
191 p_value || ',null) val' || to_char(i) || ',';
192 if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then
193 l_query(n_ind) := l_query(n_ind) || l_tmp;
194 else
195 n_left := 100 - length(l_query(n_ind));
196 l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);
197 n_ind := n_ind + 1;
198 l_query(n_ind) := substr(l_tmp, n_left + 1);
199 end if;
200
201 end loop;
202 n_ind := n_ind + 1;
203 l_query(n_ind) := '';
204 l_query(n_ind) := 'decode(' || p_pivot || ',''' || ar_col(n_cnt) ||
205 ''', rn, null) rn' || to_char(n_cnt) || ',';
206 l_query(n_ind) := l_query(n_ind) || 'decode(' || p_pivot || ',''' ||
207 ar_col(n_cnt) || ''',' || p_value || ',null) val' ||
208 to_char(n_cnt) || ' ';
209 n_ind := n_ind + 1;
210 l_query(n_ind) := '';
211 l_query(n_ind) := 'from (select ';
212
213 l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
214
215 l_query(n_ind) := l_query(n_ind) || p_pivot || ', ' || p_value ||
216 ', row_number() over(partition by ';
217
218 l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';
219
220 l_query(n_ind) := l_query(n_ind) || p_pivot || ' ';
221 n_ind := n_ind + 1;
222 l_query(n_ind) := '';
223 l_query(n_ind) := l_query(n_ind) || 'order by ' || p_value ||
224 ') rn from ' || p_tablename || ') t) t group by ';
225
226 l_query(n_ind) := l_query(n_ind) || p_anchor || ' ';
227
228-- for i in 1..n_ind loop
229-- dbms_output.put_line(l_query(i));
230-- end loop;
231
232 dbms_sql.parse(c => l_cursor,
233 statement => l_query,
234 lb => l_query.first,
235 ub => l_query.last,
236 lfflg => false,
237 language_flag => 1);
238
239 n_result := dbms_sql.execute(c => l_cursor);
240 dbms_sql.close_cursor(c => l_cursor);
241 end;
242end pkg_pivot;
243/
CREATE OR REPLACE PACKAGE pkg_pivot2
AS3
/******************************************************************************
4
NAME: pkg_pivot5
PURPOSE:6
7
REVISIONS:8
Ver Date Author Description9
--------- ---------- --------------- ------------------------------------10
1.0 2005-12-21 ERN 1. 创建包11
2.0 2005-12-22 ERN 2. 增加pivot_long过程，处理超过200列12
情况，但仍存在限制13
******************************************************************************/14
TYPE refcursor IS REF CURSOR;15
TYPE ARRAY IS TABLE OF VARCHAR2(30) index by binary_integer;16
PROCEDURE pivot(p_tablename varchar2,17
p_anchor varchar2,18
p_pivot varchar2,19
p_value varchar2,20
p_cursor OUT refcursor);21
PROCEDURE pivot_long(p_tablename varchar2,22
p_anchor varchar2,23
p_pivot varchar2,24
p_value varchar2);25
END;26
/27
create or replace package body pkg_pivot as28
procedure pivot(p_tablename varchar2, --表名，也可以输入查询29
p_anchor varchar2, --不变的列名，对于多个列可以用逗号分隔30
p_pivot varchar2, --将取值转换成列的列名31
p_value varchar2, --填充的值字段32
p_cursor out refcursor --返回结果集33
) as34
/******************************************************************************35
NAME: pivot36
PURPOSE: 竖表转横表，此过程返回结果集，仅处理组合后32767字节以下的语句。37
即大约可以处理200列的转换。38
39
REVISIONS:40
Ver Date Author Description41
--------- ---------- --------------- ------------------------------------42
1.0 2005-12-21 ERN 1. 创建43
44
******************************************************************************/45
ar_col array; --存放转换后的列名46
n_cnt number;47
l_query varchar2(32766); --最终的执行语句48
l_query_pivot varchar2(200); --统计p_pivot转换后的列数与取值49
begin50
n_cnt := 0;51
l_query := 'select ' || p_anchor || ',';52
l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||53
p_tablename;54
open p_cursor for l_query_pivot;55
56
loop57
exit when p_cursor%NOTFOUND;58
n_cnt := n_cnt + 1;59
fetch p_cursor60
into ar_col(n_cnt);61
end loop;62
n_cnt := n_cnt - 1;63
close p_cursor;64
65
for i in 1 .. n_cnt - 1 loop66
l_query := l_query || 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';67
end loop;68
69
l_query := l_query || 'max(val' || to_char(n_cnt) || ') "' ||70
ar_col(n_cnt) || '" ';71
l_query := l_query || 'from (select ';72
73
l_query := l_query || p_anchor || ', ';74
75
for i in 1 .. n_cnt - 1 loop76
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) ||77
',rn, null) rn' || to_char(i) || ',';78
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(i) || ',' ||79
p_value || ',null) val' || to_char(i) || ',';80
81
end loop;82
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) ||83
', rn, null) rn' || to_char(n_cnt) || ',';84
l_query := l_query || 'decode(' || p_pivot || ',' || ar_col(n_cnt) || ',' ||85
p_value || ',null) val' || to_char(n_cnt) || ' ';86
87
l_query := l_query || 'from (select ';88
89
l_query := l_query || p_anchor || ', ';90
91
l_query := l_query || p_pivot || ', ' || p_value ||92
', row_number() over(partition by ';93
94
l_query := l_query || p_anchor || ', ';95
96
l_query := l_query || p_pivot || ' ';97
l_query := l_query || 'order by ' || p_value || ') rn from ' ||98
p_tablename || ') t) t group by ';99
100
l_query := l_query || p_anchor || ' ';101
102
execute immediate 'alter session set cursor_sharing=force';103
104
open p_cursor for l_query;105
106
execute immediate 'alter session set cursor_sharing=exact';107
108
end;109
procedure pivot_long(p_tablename varchar2,110
p_anchor varchar2,111
p_pivot varchar2,112
p_value varchar2) as113
/******************************************************************************114
NAME: pivot_long115
PURPOSE: 竖表转横表，此过程建立_ext表，用于处理组合后查询语句超过32676116
字节的情况；对超长语句必须用dbms_sql进行解析、执行。但此处还受117
聚集过程中限制，对于过长的分组聚集语句会报ORA-01467，内部机制是118
受块大小影响，除非使用16K的大块，否则无法绕过此问题。119
120
REVISIONS:121
Ver Date Author Description122
--------- ---------- --------------- ------------------------------------123
1.0 2005-12-22 ERN 1. 创建124
125
******************************************************************************/126
ar_col array;127
n_cnt number;128
l_tmp varchar2(3256);129
p_cursor refcursor;130
l_query dbms_sql.varchar2s;131
n_ind number;132
n_left number;133
l_query_pivot varchar2(3200);134
l_cursor integer default dbms_sql.open_cursor;135
n_result number;136
begin137
-- l_query(1) :='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';138
n_cnt := 0;139
140
l_query(1) := 'create table ' ||141
substr(p_tablename,142
instr(p_tablename, '.') + 1,143
instr(p_tablename, 'where') -144
instr(p_tablename, '.') - 2) || 'ext as ';145
146
n_ind := 2;147
l_query(n_ind) := 'select ' || p_anchor || ',';148
l_query_pivot := 'select distinct ' || p_pivot || ' from ' ||149
p_tablename;150
open p_cursor for l_query_pivot;151
152
loop153
exit when p_cursor%NOTFOUND;154
n_cnt := n_cnt + 1;155
fetch p_cursor156
into ar_col(n_cnt);157
end loop;158
n_cnt := n_cnt - 1;159
close p_cursor;160
161
162
n_ind := n_ind + 1;163
l_query(n_ind) := '';164
for i in 1 .. n_cnt - 1 loop165
l_tmp := 'max(val' || to_char(i) || ') "' || ar_col(i) || '",';166
-- if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 255 then167
if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then168
-- gl dbms_output.put_line(l_query(n_ind));169
-- gl dbms_output.put_line(lengthb(l_query(n_ind)));170
l_query(n_ind) := l_query(n_ind) || l_tmp;171
else172
n_left := 100 - length(l_query(n_ind)); 173
l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);174
n_ind := n_ind + 1;175
l_query(n_ind) := substr(l_tmp, n_left + 1);176
end if;177
end loop;178
n_ind := n_ind + 1;179
l_query(n_ind) := '';180
181
l_query(n_ind) := 'max(val' || to_char(n_cnt) || ') "' || ar_col(n_cnt) || '" ';182
l_query(n_ind) := l_query(n_ind) || 'from (select ';183
184
l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';185
n_ind := n_ind + 1;186
l_query(n_ind) := '';187
for i in 1 .. n_cnt - 1 loop188
l_tmp := 'decode(' || p_pivot || ',''' || ar_col(i) || ''',rn, null) rn' ||189
to_char(i) || ',';190
l_tmp := l_tmp || 'decode(' || p_pivot || ',''' || ar_col(i) || ''',' ||191
p_value || ',null) val' || to_char(i) || ',';192
if NVL(length(l_query(n_ind)), 0) + length(l_tmp) <= 100 then193
l_query(n_ind) := l_query(n_ind) || l_tmp;194
else195
n_left := 100 - length(l_query(n_ind)); 196
l_query(n_ind) := l_query(n_ind) || substr(l_tmp, 1, n_left);197
n_ind := n_ind + 1;198
l_query(n_ind) := substr(l_tmp, n_left + 1);199
end if;200
201
end loop;202
n_ind := n_ind + 1;203
l_query(n_ind) := '';204
l_query(n_ind) := 'decode(' || p_pivot || ',''' || ar_col(n_cnt) ||205
''', rn, null) rn' || to_char(n_cnt) || ',';206
l_query(n_ind) := l_query(n_ind) || 'decode(' || p_pivot || ',''' ||207
ar_col(n_cnt) || ''',' || p_value || ',null) val' ||208
to_char(n_cnt) || ' ';209
n_ind := n_ind + 1;210
l_query(n_ind) := '';211
l_query(n_ind) := 'from (select ';212
213
l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';214
215
l_query(n_ind) := l_query(n_ind) || p_pivot || ', ' || p_value ||216
', row_number() over(partition by ';217
218
l_query(n_ind) := l_query(n_ind) || p_anchor || ', ';219
220
l_query(n_ind) := l_query(n_ind) || p_pivot || ' ';221
n_ind := n_ind + 1;222
l_query(n_ind) := '';223
l_query(n_ind) := l_query(n_ind) || 'order by ' || p_value ||224
') rn from ' || p_tablename || ') t) t group by ';225
226
l_query(n_ind) := l_query(n_ind) || p_anchor || ' ';227
228
-- for i in 1..n_ind loop229
-- dbms_output.put_line(l_query(i));230
-- end loop;231
232
dbms_sql.parse(c => l_cursor,233
statement => l_query,234
lb => l_query.first,235
ub => l_query.last,236
lfflg => false,237
language_flag => 1);238
239
n_result := dbms_sql.execute(c => l_cursor);240
dbms_sql.close_cursor(c => l_cursor);241
end;242
end pkg_pivot;243
/此项的引用通告 URL 是：
http://yaoyp.spaces.live.com/blog/cns!ac5fd97b8a549660!534.trak
引用此项的日志