SQL查询和优化（八)

列转行

测试数据如下：
DROP TABLE TEST PURGE;
CREATE TABLE test AS
SELECT * 
    FROM (SELECT deptno,sal FROM emp)
    POVIT ( COUNT(*) AS ct,SUM(sal) AS s
        FOR deptno 
        IN(10 AS deptno_10, 20 AS deptno_20, 30 AS deptno_30)
    )

要求把三个部门的“人次”转为一列显示。以前可用UNION　ALL　来写
SELECT '10' AS 部门编码,DEPTNO_10_CT AS 人次 FROM test 
UNION　ALL
SELECT '２0' AS 部门编码,DEPTNO_10_CT AS 人次 FROM test 
UNION　ALL
SELECT '３0' AS 部门编码,DEPTNO_10_CT AS 人次 FROM test 

用UNPIVOT就比较简单，语法如下：
SELECT ....  
FROM <table-expr>  
   UNPIVOT [include nulls|exclude nulls]  
     (  
      (<column>)  
      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)  
        ) AS <alias>  
WHERE .....  

实例：

SELECT *
    FROM test 
        UPIVOT(人次 FOR deptno IN(deptno_10_CT,deptno_20_CT,deptno_30_CT));

将结果集反向转置为一列

SELECT  emps
    FROM (SELECT ename,job,to_char(sal) AS sal, null AS T_COL)
    UNPOVIT INCLUDE NULLS (emps FOR COL IN (ename,job,sal,T_COL));

EMPS
________
CLARK
MANAGER
2450

KING
PRESIDENT
5000

MILLER 
CLERK
1300 

已选择12行

选自《Oracle 查询优化改写技巧与案例》 有教无类 落落 著