1.经典的实现方式(主要是利用decode函数、聚合函数（如max、sum等）、group by分组实现)
SELECT T.DEPTNO,COUNT(DECODE(T.JOB,'CLERK',1)) CLERK,COUNT(DECODE(T.JOB,'SALESMAN',1)) SALESMAN
,COUNT(DECODE(T.JOB,'PRESIDENT',1)) PRESIDENT,COUNT(DECODE(T.JOB,'MANAGER',1)) MANAGER,COUNT(DECODE(T.JOB,'ANALYST',1)) ANALYST
FROM SCOTT.EMP T GROUP BY T.DEPTNO;
SELECT T.JOB,COUNT(DECODE(T.DEPTNO,10,1)) 部门10,COUNT(DECODE(T.DEPTNO,20,1)) 部门20,COUNT(DECODE(T.DEPTNO,30,1)) 部门30,COUNT(DECODE(T.DEPTNO,40,1)) 部门40
FROM SCOTT.EMP T GROUP BY T.JOB;2.pivot,Oracle 11g后，出现PIVOT，更简便地实现行转列
WITH TMP AS(SELECT T.JOB,T.DEPTNO FROM SCOTT.EMP T)
SELECT * FROM TMP T PIVOT(COUNT(1) FOR JOB IN ('CLERK' 店员,'SALESMAN' 销售,'PRESIDENT' 总裁,'MANAGER' 经理,'ANALYST' 分析员));
WITH TMP AS(SELECT T.JOB,T.DEPTNO FROM SCOTT.EMP T)
SELECT * FROM TMP T PIVOT(COUNT(1) FOR DEPTNO IN (10 部门10,20 部门20,30 部门30,40 部门40));3.使用max结合decode函数
WITH TMP AS(SELECT '四川省' NATION,'成都市' CITY,'第一' RANKING FROM DUAL UNION ALL SELECT '四川省' NATION,'绵阳市' CITY,'第二' RANKING FROM DUAL UNION ALL SELECT '湖北省' NATION,'武汉市' CITY,'第一' RANKING FROM DUAL UNION ALL SELECT '湖北省' NATION,'宜昌市' CITY,'第二' RANKING FROM DUAL UNION ALL SELECT '湖北省' NATION,'襄阳市' CITY,'第三' RANKING FROM DUAL)
SELECT NATION,MAX(DECODE(RANKING,'第一',CITY,'')) 第一,MAX(DECODE(RANKING,'第二',CITY,'')) 第二,MAX(DECODE(RANKING,'第三',CITY,'')) 第三 FROM TMP GROUP BY NATION;
