pivot and unpivot queries in 11g-优快云博客

本文通过具体示例展示了如何在Oracle数据库中使用PIVOT操作来汇总数据。示例中使用了SCOTT.EMP表，对不同部门按职位进行薪资求和，并展示了两种PIVOT用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考：

http://www.oracle-developer.net/display.php?id=506

http://blog.youkuaiyun.com/tianlesoftware/article/details/7060306

sys@129.100.253.79> select *from scott.emp;

     EMPNO ENAME      JOB              MGR HIRED        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 00:00        800                    20
      7499 ALLEN      SALESMAN        7698 00:00       1600        300         30
      7521 WARD       SALESMAN        7698 00:00       1250        500         30
      7566 JONES      MANAGER         7839 00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 00:00       2850                    30
      7782 CLARK      MANAGER         7839 00:00       2450                    10
      7788 SCOTT      ANALYST         7566 00:00       3000                    20
      7839 KING       PRESIDENT            00:00       5000                    10
      7844 TURNER     SALESMAN        7698 00:00       1500          0         30
      7876 ADAMS      CLERK           7788 00:00       1100                    20
      7900 JAMES      CLERK           7698 00:00        950                    30
      7902 FORD       ANALYST         7566 00:00       3000                    20
      7934 MILLER     CLERK           7782 00:00       1300                    10

14 rows selected.

Elapsed: 00:00:00.01
sys@129.100.253.79> SELECT *
2 FROM (SELECT deptno, job, sal FROM scott.emp)
3 PIVOT (SUM (sal)
4 FOR deptno
5 IN (10, 20, 30, 40)
6 );

JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
MANAGER         2450       2975       2850
ANALYST                    6000
PRESIDENT       5000
SALESMAN                              5600
CLERK           1300       1900        950

Elapsed: 00:00:00.01
sys@129.100.253.79> SELECT *
2      FROM   scott.emp
3      PIVOT (SUM(sal)
4      FOR    deptno IN (10,20,30,40));

     EMPNO ENAME      JOB              MGR HIRED       COMM         10         20         30         40
---------- ---------- --------- ---------- ----- ---------- ---------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 00:00                                        2850
      7654 MARTIN     SALESMAN        7698 00:00       1400                             1250
      7900 JAMES      CLERK           7698 00:00                                         950
      7782 CLARK      MANAGER         7839 00:00                  2450
      7876 ADAMS      CLERK           7788 00:00                             1100
      7902 FORD       ANALYST         7566 00:00                             3000
      7521 WARD       SALESMAN        7698 00:00        500                             1250
      7844 TURNER     SALESMAN        7698 00:00          0                             1500
      7566 JONES      MANAGER         7839 00:00                             2975
      7369 SMITH      CLERK           7902 00:00                              800
      7499 ALLEN      SALESMAN        7698 00:00        300                             1600
      7934 MILLER     CLERK           7782 00:00                  1300
      7788 SCOTT      ANALYST         7566 00:00                             3000
      7839 KING       PRESIDENT            00:00                  5000

14 rows selected.

Elapsed: 00:00:00.01
sys@129.100.253.79>