---使用DECODE函数进行转换
SCOTT@PROD5> select deptno, job, sum(sal) from emp group by deptno, job;

    DEPTNO JOB       SUM(SAL)
---------- --------- ----------
20 CLERK   1900
30 SALESMAN   5600
20 MANAGER   2975
30 CLERK    950
10 PRESIDENT   5000
  CLERK    800
30 MANAGER   2850
10 CLERK   1300
10 MANAGER   2450
20 ANALYST   6000

10 rows selected.

SCOTT@PROD5> select deptno,
  2  sum(decode(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,
  3  sum(decode(job,'MANAGER',sal,0)) MANAGER_JOB,
  4  sum(decode(job,'ANALYST',sal,0)) ANALYST_JOB,
  5  sum(decode(job,'SALESMAN',sal,0)) SALESMAN_JOB,
  6  sum(decode(job,'CLERK',sal,0)) CLERK_JOB
  7  from emp  
  8  group by deptno;

    DEPTNO PRESIDENT_JOB MANAGER_JOB ANALYST_JOB SALESMAN_JOB  CLERK_JOB
---------- ------------- ----------- ----------- ------------ ----------
30       0 2850       0 5600     950
      0   0       0    0     800
20       0 2975    6000    0    1900
10    5000 2450       0    0    1300

---使用PIVOT函数进行转换
SCOTT@PROD5> select * from (select deptno, job, sal from emp)
  2  PIVOT(
  3  sum(sal)
  4  for job in(
  5  'PRESIDENT' as PRESIDENT_JOB,
  6  'MANAGER' as MANAGER_JOB,
  7  'ANALYST' as ANALYST_JOB,
  8  'SALESMAN' as SALESMAN_JOB,
  9  'CLERK' as CLERK_JOB
 10  )
 11  ) order by deptno;

    DEPTNO PRESIDENT_JOB MANAGER_JOB ANALYST_JOB SALESMAN_JOB  CLERK_JOB
---------- ------------- ----------- ----------- ------------ ----------
10    5000 2450    1300
20 2975    6000    1900
30 2850 5600     950
    800

---使用PIVOT函数进行多列转换  
SCOTT@PROD5> alter table emp add(sex varchar2(10) default 'MEN');

Table altered.

SCOTT@PROD5> update emp set sex='WOMEN' where to_char(hiredate,'yyyy')='1981';

10 rows updated.

SCOTT@PROD5> commit;

Commit complete.

SCOTT@PROD5> select empno, ename, sex from emp;

     EMPNO ENAME      SEX
---------- ---------- ----------
      7369 SMITH      MEN
      7499 ALLEN      WOMEN
      7521 WARD       WOMEN
      7566 JONES      WOMEN
      7654 MARTIN     WOMEN
      7698 BLAKE      WOMEN
      7782 CLARK      WOMEN
      7788 SCOTT      MEN
      7839 KING       WOMEN
      7844 TURNER     WOMEN
      7876 ADAMS      MEN
      7900 JAMES      WOMEN
      7902 FORD       WOMEN
      7934 MILLER     MEN
      8888 LLF      MEN

15 rows selected.

SCOTT@PROD5> select * from ( select deptno, job, sal, sex from emp)
  2  PIVOT (
  3  sum(sal) as sum_sal
  4  for(job,sex) in (
  5  ('MANAGER','MEN') as manager_male_job,
  6  ('MANAGER','WOMEN') as manager_female_job,
  7  ('CLERK','MEN') as clerk_male_job,
  8  ('CLERK','WOMEN') as clerk_female_job
  9  )
 10  ) ORDER BY deptno;

    DEPTNO MANAGER_MALE_JOB_SUM_SAL MANAGER_FEMALE_JOB_SUM_SAL CLERK_MALE_JOB_SUM_SAL CLERK_FEMALE_JOB_SUM_SAL
---------- ------------------------ -------------------------- ---------------------- ------------------------
10  2450 1300
20  2975 1900
30  2850   950
 800