oracle中的行列转换（二 行转列 unpivot）

在上次的列转行的基础之上:

SELECT *
FROM
 (SELECT deptno, empjob, sal FROM emp
 ) pivot( SUM(sal) FOR empjob IN ( 'PRESIDENT' AS PRESIDENT, 'MANAGER' AS MANAGER, 'ANALYST' AS ANALYST, 'CLERK' AS CLERK, 'SALESMAN' AS SALESMAN ) )
ORDER BY deptno

将上表作为基础表,做行转列。如下（默认去除空值）：

SELECT *
FROM
 (SELECT *
FROM
 (SELECT deptno, empjob, sal FROM emp
 ) pivot( SUM(sal) FOR empjob IN ( 'PRESIDENT' AS PRESIDENT, 'MANAGER' AS MANAGER, 'ANALYST' AS ANALYST, 'CLERK' AS CLERK, 'SALESMAN' AS SALESMAN ) )
ORDER BY deptno
 ) UNPIVOT ( sal_sum FOR empjob IN (PRESIDENT, MANAGER, ANALYST, CLERK,SALESMAN) ); 

结果如下：

下面为包含空值：

SELECT *
FROM
 (SELECT *
FROM
 (SELECT deptno, empjob, sal FROM emp
 ) pivot( SUM(sal) FOR empjob IN ( 'PRESIDENT' AS PRESIDENT, 'MANAGER' AS MANAGER, 'ANALYST' AS ANALYST, 'CLERK' AS CLERK, 'SALESMAN' AS SALESMAN ) )
ORDER BY deptno
 ) UNPIVOT include nulls ( sal_sum FOR empjob IN (PRESIDENT, MANAGER, ANALYST, CLERK,SALESMAN) ); 

效果如下：