在11g以前，行列转化是一个比较麻烦的事情。对于列转行来说，以前只能使用UNION ALL语句，显得十分的麻烦，11g提供了UNPIVOT语句，可以很方便的解决这个问题。
先做一个测试表，利用上一篇介绍的PIVOT语句：
SQL> CREATE TABLE T_PIVOT AS SELECT * 2 FROM (SELECT OWNER, SEGMENT_TYPE, BYTES FROM DBA_SEGMENTS) 3 PIVOT (SUM(BYTES) FOR SEGMENT_TYPE IN 4 ('TABLE' TABLE_SIZE, 'TABLE PARTITION' TABLE_PART_SIZE, 5 'INDEX' INDEX_SIZE, 'INDEX PARTITION' INDEX_PART_SIZE)); 表已创建。 SQL> SELECT * FROM T_PIVOT; OWNER TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE ------------------------------ ---------- --------------- ---------- --------------- WKSYS 2621440 5177344 MDSYS 11993088 131072 6881280 393216 YANGTK 2031616 65536 TSMSYS 65536 65536 WK_TEST 5767168 6553600 OUTLN 196608 262144 CTXSYS 2031616 3407872 OLAPSYS 7471104 8847360 FLOWS_FILES 65536 262144 SYSTEM 7077888 2490368 9568256 3276800 EXFSYS 1310720 2490368 DBSNMP 1048576 524288 ORDSYS 3670016 5832704 SYSMAN 53608448 69402624 XDB 17170432 7471104 FLOWS_030000 41680896 54329344 SYS 547356672 9043968 165216256 8388608 WMSYS 2424832 3866624 已选择18行。
在10g及以前版本要实现列转行：
SQL> SELECT OWNER, 'TABLE' OBJECT_TYPE, TABLE_SIZE BYTES FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK') 2 UNION ALL 3 SELECT OWNER, 'TABLE PARTITION', TABLE_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK') 4 UNION ALL 5 SELECT OWNER, 'INDEX', INDEX_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK') 6 UNION ALL 7 SELECT OWNER, 'INDEX PARTITION', INDEX_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK'); OWNER OBJECT_TYPE BYTES ------------------------------ --------------- ---------- YANGTK TABLE 2031616 SYS TABLE 547356672 YANGTK TABLE PARTITION SYS TABLE PARTITION 9043968 YANGTK INDEX 65536 SYS INDEX 165216256 YANGTK INDEX PARTITION SYS INDEX PARTITION 8388608 已选择8行。
这种方法相对来说比较麻烦，用UNPIVOT则会简化很多：
SQL> SELECT OWNER, OBJECT_TYPE, BYTES FROM T_PIVOT 2 UNPIVOT INCLUDE NULLS (BYTES FOR OBJECT_TYPE IN 3 (TABLE_SIZE AS 'TABLE', TABLE_PART_SIZE AS 'TABLE PARTITION', 4 INDEX_SIZE AS 'INDEX', INDEX_PART_SIZE AS 'INDEX PARTITION')) 5 WHERE OWNER IN ('SYS', 'YANGTK'); OWNER OBJECT_TYPE BYTES ------------------------------ --------------- ---------- YANGTK TABLE 2031616 YANGTK TABLE PARTITION YANGTK INDEX 65536 YANGTK INDEX PARTITION SYS TABLE 547356672 SYS TABLE PARTITION 9043968 SYS INDEX 165216256 SYS INDEX PARTITION 8388608 已选择8行。
不光是语法上的简化，从执行计划和统计信息上看：
SQL> SET AUTOT TRACE SQL> SELECT OWNER, 'TABLE' OBJECT_TYPE, TABLE_SIZE BYTES FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK') 2 UNION ALL 3 SELECT OWNER, 'TABLE PARTITION', TABLE_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK') 4 UNION ALL 5 SELECT OWNER, 'INDEX', INDEX_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK') 6 UNION ALL 7 SELECT OWNER, 'INDEX PARTITION', INDEX_PART_SIZE FROM T_PIVOT WHERE OWNER IN ('SYS', 'YANGTK'); 已选择8行。 执行计划 ---------------------------------------------------------- Plan hash value: 634273332 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 8 | 240 | 12 (75)| 00:00:01 | | 1 | UNION-ALL | | | | | | |* 2 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL| T_PIVOT | 2 | 60 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"='SYS' OR "OWNER"='YANGTK') 3 - filter("OWNER"='SYS' OR "OWNER"='YANGTK') 4 - filter("OWNER"='SYS' OR "OWNER"='YANGTK') 5 - filter("OWNER"='SYS' OR "OWNER"='YANGTK') Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 620 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed SQL> SELECT OWNER, OBJECT_TYPE, BYTES FROM T_PIVOT 2 UNPIVOT INCLUDE NULLS (BYTES FOR OBJECT_TYPE IN 3 (TABLE_SIZE AS 'TABLE', TABLE_PART_SIZE AS 'TABLE PARTITION', 4 INDEX_SIZE AS 'INDEX', INDEX_PART_SIZE AS 'INDEX PARTITION')) 5 WHERE OWNER IN ('SYS', 'YANGTK'); 已选择8行。 执行计划 ---------------------------------------------------------- Plan hash value: 2063660069 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 273 | 12 (0)| 00:00:01 | | 1 | VIEW | | 7 | 273 | 12 (0)| 00:00:01 | | 2 | UNPIVOT | | | | | | |* 3 | TABLE ACCESS FULL| T_PIVOT | 1 | 69 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T_PIVOT"."OWNER"='SYS' OR "T_PIVOT"."OWNER"='YANGTK') Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 631 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed
如果不使用UNPIVOT，那么需要多个UNION ALL，多次全表扫描。而对于UNPIVOT操作，只需要一个全表扫描就可以了。
对于UNPIVOT还可以不选择为NULL的结果，将上面的INCLUDE NULLS去掉就可以了：
SQL> SET AUTOT OFF SQL> SELECT OWNER, OBJECT_TYPE, BYTES FROM T_PIVOT 2 UNPIVOT (BYTES FOR OBJECT_TYPE IN 3 (TABLE_SIZE AS 'TABLE', TABLE_PART_SIZE AS 'TABLE PARTITION', 4 INDEX_SIZE AS 'INDEX', INDEX_PART_SIZE AS 'INDEX PARTITION')) 5 WHERE OWNER IN ('SYS', 'YANGTK'); OWNER OBJECT_TYPE BYTES ------------------------------ --------------- ---------- YANGTK TABLE 2031616 YANGTK INDEX 65536 SYS TABLE 547356672 SYS TABLE PARTITION 9043968 SYS INDEX 165216256 SYS INDEX PARTITION 8388608 已选择6行。
如果不指定列对应的常量，那么会直接将列名作为分类的名称：
