[font=Tahoma,][size=26px]Oracle 列转行函数pivot[font=Tahoma,] 作为数据库应用开发人员，我们有很大的精力应付在处理各种各样的数据类型，展现各种维度的报表上面。

[font=Arial, Helvetica, sans-serif][color=#09999]]行转列、列转行是我们经常会遇到的“诡异”需求。标准SQL没有提供此类型操作的支持函数，早期Oracle的版本中，我们也只能通过复杂的变通手段实现。在Oracle11g中，这种情况改变了，我们可以利用[color=#09999]]pivot函数轻松实现行列互转并且聚合的查询需求。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]1、环境准备[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]我们选择Oracle 11g进行实验。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> select * from v$version;[font=Arial, Helvetica, sans-serif]BANNER[font=Arial, Helvetica, sans-serif]--------------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production[font=Arial, Helvetica, sans-serif]PL/SQL Release 11.2.0.1.0 - Production[font=Arial, Helvetica, sans-serif]CORE11.2.0.1.0Production[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]创建数据表T，进行有选择的数据筛选。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> create table t (owner varchar2(30), object_type varchar2(100), value number);[font=Arial, Helvetica, sans-serif]Table created[font=Arial, Helvetica, sans-serif]SQL> insert into t select owner, object_type, object_id from dba_objects where object_type in ('TABLE','INDEX');[font=Arial, Helvetica, sans-serif]10386 rows inserted[font=Arial, Helvetica, sans-serif]SQL> commit;[font=Arial, Helvetica, sans-serif]Commit complete[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]每个owner最多有两种类型的type值，每个owner和object_type组合包括多条记录。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> select * from t where rownum<5;[font=Arial, Helvetica, sans-serif]OWNER OBJECT_TYP VALUE[font=Arial, Helvetica, sans-serif]----- ---------- ----------[font=Arial, Helvetica, sans-serif]SYS TABLE 20[font=Arial, Helvetica, sans-serif]SYS INDEX 46[font=Arial, Helvetica, sans-serif]SYS TABLE 28[font=Arial, Helvetica, sans-serif]SYS TABLE 15[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]要求以owner和object_type进行汇总，并且将TABLE和INDEX分别成列，显示汇总值。简而言之，就是一个owner一行记录，每列对应一种object_type取值。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]2、行转列处理[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]按照抽丝剥茧的原则，我们聚合一次owner和object_type。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]--聚合汇总结[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> select owner, object_type, sum(value) from t group by owner, object_type;[font=Arial, Helvetica, sans-serif]OWNER OBJECT_TYP SUM(VALUE)[font=Arial, Helvetica, sans-serif]-------------------- ---------- ----------[font=Arial, Helvetica, sans-serif]SCOTT INDEX 146392[font=Arial, Helvetica, sans-serif]PRITEST TABLE 172006[font=Arial, Helvetica, sans-serif]（篇幅原因，有省略......）[font=Arial, Helvetica, sans-serif]DICT INDEX 18490973[font=Arial, Helvetica, sans-serif]COMMON TABLE 9076700[font=Arial, Helvetica, sans-serif]63 rows selected[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]下面就是避免不了的行转列操作了。在[color=#09999]]11g以前的版本中，我们可能需要借助专门的复杂SQL书写乃至存储过程才能解决。在11g中，我们可以使用pivot函数，一次性的将聚合操作和行转列操作完成。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]select *[font=Arial, Helvetica, sans-serif]from t[font=Arial, Helvetica, sans-serif]pivot [font=Arial, Helvetica, sans-serif]([font=Arial, Helvetica, sans-serif] sum(value) --聚合操作函数[font=Arial, Helvetica, sans-serif] for object_type --行转列标准[font=Arial, Helvetica, sans-serif] in ('TABLE','INDEX') --行转列列取值和顺序[font=Arial, Helvetica, sans-serif])[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]上面的SQL就是pivot函数的基本格式。看起来非常简单，主要要定义三个部分的内容。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]ü 聚合列取值。需要告诉pivot函数进行转列的过程中，聚合操作的函数和处理对象；[font=Arial, Helvetica, sans-serif]ü 行转列标准。依据那个列进行行转列；[font=Arial, Helvetica, sans-serif]ü 列转行取值。因为要将数据行取值转成列，我们需要告诉Oracle那些取值成列，并且这些取值成列的过程中，列顺序是如何的；[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]下面我们看执行结果。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> select *[font=Arial, Helvetica, sans-serif] 2 from t[font=Arial, Helvetica, sans-serif] 3 pivot[font=Arial, Helvetica, sans-serif] 4 ([font=Arial, Helvetica, sans-serif] 5 sum(value)[font=Arial, Helvetica, sans-serif] 6 for object_type[font=Arial, Helvetica, sans-serif] 7 in ('TABLE','INDEX')[font=Arial, Helvetica, sans-serif] 8 )[font=Arial, Helvetica, sans-serif] 9 ;[font=Arial, Helvetica, sans-serif]OWNER 'TABLE' 'INDEX'[font=Arial, Helvetica, sans-serif]-------------------- ---------- ----------[font=Arial, Helvetica, sans-serif]MDSYS 8003137 7378327[font=Arial, Helvetica, sans-serif]MELODY 299266 [font=Arial, Helvetica, sans-serif]RMAN 8292775 18094518[font=Arial, Helvetica, sans-serif]OUTLN 1351 1805[font=Arial, Helvetica, sans-serif]（篇幅原因，有省略......）[font=Arial, Helvetica, sans-serif]COMMON 9076700 17161092[font=Arial, Helvetica, sans-serif]34 rows selected[font=Arial, Helvetica, sans-serif]处理成功，我们查看一下执行计划。[font=Arial, Helvetica, sans-serif]SQL> explain plan for select *[font=Arial, Helvetica, sans-serif] 2 from t[font=Arial, Helvetica, sans-serif] 3 pivot[font=Arial, Helvetica, sans-serif] 4 ([font=Arial, Helvetica, sans-serif] 5 sum(value)[font=Arial, Helvetica, sans-serif] 6 for object_type[font=Arial, Helvetica, sans-serif] 7 in ('TABLE','INDEX')[font=Arial, Helvetica, sans-serif] 8 );[font=Arial, Helvetica, sans-serif]Explained[font=Arial, Helvetica, sans-serif]SQL> select * from table(dbms_xplan.display);[font=Arial, Helvetica, sans-serif]PLAN_TABLE_OUTPUT[font=Arial, Helvetica, sans-serif]--------------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]Plan hash value: 3924414983[font=Arial, Helvetica, sans-serif]----------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |[font=Arial, Helvetica, sans-serif]----------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]| 0 | SELECT STATEMENT | | 34 | 612 | 13 (8)| 00:00:01 |[font=Arial, Helvetica, sans-serif]| 1 | HASH GROUP BY PIVOT| | 34 | 612 | 13 (8)| 00:00:01 |[font=Arial, Helvetica, sans-serif]| 2 | TABLE ACCESS FULL | T | 10386 | 182K| 12 (0)| 00:00:01 |[font=Arial, Helvetica, sans-serif]----------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]9 rows selected[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]从执行计划上看，Oracle为了pivot函数引入了一个专门操作pivot。执行计划中的hast group by pivot操作就是将数据转移到专门的处理区（猜想是PGA）进行流程化的处理。[font=Arial, Helvetica, sans-serif]下面是使用autotrace处理过的情况。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> set autotrace traceonly;[font=Arial, Helvetica, sans-serif]SQL> select *[font=Arial, Helvetica, sans-serif] 2 from t[font=Arial, Helvetica, sans-serif] 3 pivot[font=Arial, Helvetica, sans-serif] 4 ([font=Arial, Helvetica, sans-serif] 5 sum(value)[font=Arial, Helvetica, sans-serif] 6 for object_type[font=Arial, Helvetica, sans-serif] 7 in ('TABLE','INDEX')[font=Arial, Helvetica, sans-serif] 8 );[font=Arial, Helvetica, sans-serif]已选择34行。[font=Arial, Helvetica, sans-serif]执行计划[font=Arial, Helvetica, sans-serif]----------------------------------------------------------[font=Arial, Helvetica, sans-serif]Plan hash value: 3924414983[font=Arial, Helvetica, sans-serif]----------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |[font=Arial, Helvetica, sans-serif]----------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]| 0 | SELECT STATEMENT | | 34 | 612 | 13 (8)| 00:00:01 |[font=Arial, Helvetica, sans-serif]| 1 | HASH GROUP BY PIVOT| | 34 | 612 | 13 (8)| 00:00:01 |[font=Arial, Helvetica, sans-serif]| 2 | TABLE ACCESS FULL | T | 10386 | 182K| 12 (0)| 00:00:01 |[font=Arial, Helvetica, sans-serif]----------------------------------------------------------------------------[font=Arial, Helvetica, sans-serif]统计信息[font=Arial, Helvetica, sans-serif]----------------------------------------------------------[font=Arial, Helvetica, sans-serif] 196 recursive calls[font=Arial, Helvetica, sans-serif] 0 db block gets[font=Arial, Helvetica, sans-serif] 65 consistent gets[font=Arial, Helvetica, sans-serif] 42 physical reads[font=Arial, Helvetica, sans-serif] 0 redo size[font=Arial, Helvetica, sans-serif] 1503 bytes sent via SQL*Net to client[font=Arial, Helvetica, sans-serif] 441 bytes received via SQL*Net from client[font=Arial, Helvetica, sans-serif] 4 SQL*Net roundtrips to/from client[font=Arial, Helvetica, sans-serif] 3 sorts (memory)[font=Arial, Helvetica, sans-serif] 0 sorts (disk)[font=Arial, Helvetica, sans-serif] 34 rows processed[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]注意在统计信息中，引用了3的sorts操作。[font=Arial, Helvetica, sans-serif]应该说，pivot函数是我们可以找到比较好的行转列的操作方式了。在Oracle端，是要消耗一定的排序操作。当数据集合特别大的时候，我们可以猜测会有一些性能的退化（特别是发生sorts(disk)）。Pivot作为标准Oracle函数，Oracle也会不断进行优化处理。所以，性能一般是可以接受的。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]3、进一步的需求[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]在结果集合中，我们看到了结果标题如下：[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]OWNER 'TABLE' 'INDEX'[font=Arial, Helvetica, sans-serif]-------------------- ---------- ----------[font=Arial, Helvetica, sans-serif]MDSYS 8003137 7378327[font=Arial, Helvetica, sans-serif]MELODY 299266 [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]Oracle是使用两个列值作为标题。那么我们结果如何引用这个列[font=Arial, Helvetica, sans-serif]名称呢？[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> select owner, 'TABLE','INDEX' from ([font=Arial, Helvetica, sans-serif] 2 select *[font=Arial, Helvetica, sans-serif] 3 from t[font=Arial, Helvetica, sans-serif] 4 pivot[font=Arial, Helvetica, sans-serif] 5 ([font=Arial, Helvetica, sans-serif] 6 sum(value)[font=Arial, Helvetica, sans-serif] 7 for object_type[font=Arial, Helvetica, sans-serif] 8 in ('TABLE','INDEX')[font=Arial, Helvetica, sans-serif] 9 )) where rownum<3;[font=Arial, Helvetica, sans-serif]OWNER 'TABLE' 'INDEX'[font=Arial, Helvetica, sans-serif]-------------------- ------- -------[font=Arial, Helvetica, sans-serif]APEX_030200 TABLE INDEX[font=Arial, Helvetica, sans-serif]APPQOSSYS TABLE INDEX[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]显然，Oracle将其识别为了字符串。应该怎么做呢？我们需要使用双引号。之所以Oracle会识别错误，就因为’’的常量识别优先级高于列名称优先级。我们需要让Oracle忽略这个特点，就使用双引号。[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]SQL> select owner, 'TABLE' PRI_TYP,"'TABLE'" PRI_AMT,'INDEX' SEC_TYP,"'INDEX'" SEC_AMT from ([font=Arial, Helvetica, sans-serif] 2 select *[font=Arial, Helvetica, sans-serif] 3 from t[font=Arial, Helvetica, sans-serif] 4 pivot[font=Arial, Helvetica, sans-serif] 5 ([font=Arial, Helvetica, sans-serif] 6 sum(value)[font=Arial, Helvetica, sans-serif] 7 for object_type[font=Arial, Helvetica, sans-serif] 8 in ('TABLE','INDEX')[font=Arial, Helvetica, sans-serif] 9 )) where rownum<3;[font=Arial, Helvetica, sans-serif]OWNER PRI_TYP PRI_AMT SEC_TYP SEC_AMT[font=Arial, Helvetica, sans-serif]-------------------- ------- ---------- ------- ----------[font=Arial, Helvetica, sans-serif]APEX_030200 TABLE 25757431 INDEX 67809456[font=Arial, Helvetica, sans-serif]APPQOSSYS TABLE 25887 INDEX [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]实现成功！[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]4、结论[font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif] [font=Arial, Helvetica, sans-serif]在现实工作中，我们遇到的需求千奇百怪。在选择工具的时候，要有选择类库、预定义提供的方法函数，不要轻易的选择自定义方法。因为对现成的类库和产品来说，它在实现这个方法的时候倾注了很大心血和能力在其中，其效率一定是比我们自己写的要强很多。这也就要求我们需要不断关注新特性，新方法和改进，不断的学习和进步。