SQL查询和优化（八)

列转行

测试数据如下：DROP TABLE TEST PURGE;CREATE TABLE test ASSELECT *     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　ALLSELECT '２0' AS 部门编码,DEPTNO_10_CT AS 人次 FROM test UNION　ALLSELECT '３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________CLARKMANAGER2450KINGPRESIDENT5000MILLER CLERK1300 已选择12行

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