行专列

在Oracle中，有两种方式来实现行专列，分别是CASE WHEN END 和11G版本新增的pivot函数，其中case when使用场景广泛，但是编写比较繁琐且不宜维护，pivot的编写和维护较为简单，但是使用多有限制

接下来通过案列来解析这两类的使用方法

CREATE TABLE temp AS SELECT * FROM (
SELECT '财务' AS department,'赵一' AS worker_name,'一月' AS work_month,1000 AS salary from dual UNION ALL
SELECT '财务' AS department,'赵一' AS worker_name,'二月' AS work_month,1500 AS salary from dual UNION ALL
SELECT '财务' AS department,'赵一' AS worker_name,'三月' AS work_month,500 AS salary from dual UNION ALL
SELECT '财务' AS department,'钱二' AS worker_name,'一月' AS work_month,989 AS salary from dual UNION ALL
SELECT '财务' AS department,'钱二' AS worker_name,'二月' AS work_month,718 AS salary from dual UNION ALL
SELECT '财务' AS department,'钱二' AS worker_name,'三月' AS work_month,911 AS salary from dual UNION ALL
SELECT '项目' AS department,'孙三' AS worker_name,'一月' AS work_month,2000 AS salary from dual UNION ALL
SELECT '项目' AS department,'孙三' AS worker_name,'二月' AS work_month,1800 AS salary from dual UNION ALL
SELECT '项目' AS department,'孙三' AS worker_name,'三月' AS work_month,2100 AS salary from dual UNION ALL
SELECT '项目' AS department,'李四' AS worker_name,'一月' AS work_month,1500 AS salary from dual UNION ALL
SELECT '项目' AS department,'李四' AS worker_name,'二月' AS work_month,2300 AS salary from dual UNION ALL
SELECT '项目' AS department,'李四' AS worker_name,'三月' AS work_month,1500 AS salary from dual UNION ALL
SELECT '项目' AS department,'周五' AS worker_name,'一月' AS work_month,3500 AS salary from dual UNION ALL
SELECT '项目' AS department,'周五' AS worker_name,'二月' AS work_month,2300 AS salary from dual UNION ALL
SELECT '项目' AS department,'周五' AS worker_name,'三月' AS work_month,2900 AS salary from dual UNION ALL
SELECT '研发' AS department,'吴六' AS worker_name,'一月' AS work_month,3500 AS salary from dual UNION ALL
SELECT '研发' AS department,'吴六' AS worker_name,'二月' AS work_month,3200 AS salary from dual UNION ALL
SELECT '研发' AS department,'吴六' AS worker_name,'三月' AS work_month,2900 AS salary from dual UNION ALL
SELECT '研发' AS department,'李七' AS worker_name,'一月' AS work_month,4500 AS salary from dual UNION ALL
SELECT '研发' AS department,'李七' AS worker_name,'二月' AS work_month,4200 AS salary from dual UNION ALL
SELECT '研发' AS department,'李七' AS worker_name,'三月' AS work_month,3900 AS salary from dual 
)

表temp记录的为不同部门不同员工一季度每个月的薪资情况，现要求通过sql查询：各部门一季度每月的薪资支出以及一季度的合计支出

CASE WHEN END

用于实现多条件判断，在WHEN之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理

SELECT t.department AS 部门,
sum(CASE t.work_month WHEN '一月' THEN t.salary END ) 一月支出,
sum(CASE t.work_month WHEN '二月' THEN t.salary END ) 二月支出,
sum(CASE t.work_month WHEN '三月' THEN t.salary END ) 三月支出,
SUM(t.salary)合计支出
  FROM temp t GROUP BY t.department;

decode

多值判断，如果某一个列（或某一个值）与判断值相同，则使用指定的显示结果输出，如果没有满足条件，则显示默认值

	SELECT t.department AS 部门,
sum(DECODE(t.work_month,'一月',t.salary)) 一月支出,
sum(DECODE(t.work_month,'二月',t.salary)) 二月支出,
sum(DECODE(t.work_month,'三月',t.salary)) 三月支出,
SUM(t.salary)合计支出
  FROM temp t GROUP BY t.department;

pivot

SELECT 部门,
       一月支出,
       二月支出,
       三月支出,
       (一月支出 + 二月支出 + 三月支出) 合计支出
  FROM (SELECT t.department 部门, t.work_month, SUM(t.salary) salary
          FROM temp t
         GROUP BY t.department, t.work_month) t
PIVOT(MAX(t.salary)
   FOR WORK_MONTH IN('一月' AS 一月支出,
                     '二月' AS 二月支出,
                     '三月' AS 三月支出))

通过以上案例可以看到，pivot函数对于案例的实现更加的简便，但是需要注意的是最后一列合计，通过case when可以sum聚合来快速完成，但是pivot做不到，pivot函数只能按照同一个规则分类各项数据，各列直接的数据不能交叉重复。

不同用法

案列一：

下面我们在增加列 BONUS 来记录奖金

要求在查询结果中记录每月的奖金支出，来看看两种方式的不同实现：

首先我们看case when的实现方式，通过增加三行语句来完成

SELECT t.department AS 部门,
sum(CASE t.work_month WHEN '一月' THEN t.salary END ) 一月薪资,
sum(CASE t.work_month WHEN '一月' THEN t.bonus END ) 一月奖金,
sum(CASE t.work_month WHEN '二月' THEN t.salary END ) 二月薪资,
sum(CASE t.work_month WHEN '二月' THEN t.bonus END ) 二月奖金,
sum(CASE t.work_month WHEN '三月' THEN t.salary END ) 三月薪资,
sum(CASE t.work_month WHEN '三月' THEN t.bonus END ) 三月奖金
  FROM temp t GROUP BY t.department;

但是pivot只需要增加一个设定即可完成

SELECT *
  FROM (SELECT t.department 部门,
               t.work_month,
               SUM(t.salary) salary,
               SUM(t.bonus) bonus
          FROM temp t
         GROUP BY t.department, t.work_month)
PIVOT(MAX(salary) 薪资, MAX(bonus) 支出
   FOR WORK_MONTH IN('一月' AS 一月, '二月' AS 二月, '三月' AS 三月))

案例二：多个条件的列转化

需要将部门和员工都转化为列，统计每月的薪资，那么只能使用case when 来实现，pivot则无能为力了

SELECT t.work_month AS 月份,
sum(CASE t.department WHEN '财务' THEN t.salary END ) 财务薪资,
sum(CASE t.department WHEN '研发' THEN t.salary END ) 研发薪资,
sum(CASE t.department WHEN '项目' THEN t.salary END ) 项目薪资,
sum(CASE t.worker_name WHEN '赵一' THEN t.salary END ) 赵一薪资,
sum(CASE t.worker_name WHEN '钱二' THEN t.salary END ) 钱二薪资,
sum(CASE t.worker_name WHEN '孙三' THEN t.salary END ) 孙三薪资,
sum(CASE t.worker_name WHEN '李四' THEN t.salary END ) 李四薪资,
sum(CASE t.worker_name WHEN '周五' THEN t.salary END ) 周五薪资,
sum(CASE t.worker_name WHEN '吴六' THEN t.salary END ) 吴六薪资,
sum(CASE t.worker_name WHEN '李七' THEN t.salary END ) 李七薪资
  FROM temp t GROUP BY t.work_month
	ORDER BY t.work_month

案例三：使用ANY变为XML数据显示

SELECT *
  FROM (SELECT t.department, t.worker_name,t.salary FROM temp t)
PIVOT XML(SUM(salary)
   FOR worker_name IN(ANY)
)
 ORDER BY department;

案例四：设置多个统计列

列转行

准备数据如下：

CREATE TABLE test1 AS SELECT 部门,
       一月支出,
       二月支出,
       三月支出
  FROM (SELECT t.department 部门, t.work_month, SUM(t.salary) salary
          FROM temp t
         GROUP BY t.department, t.work_month) t
PIVOT(MAX(t.salary)
   FOR WORK_MONTH IN('一月' AS 一月支出,
                     '二月' AS 二月支出,
                     '三月' AS 三月支出));

需要将支出转为列 类别显示

先看下使用union 来查询，可以看到语句扫描了三次test1表，且如果列数过多，编写和维护就显得极为繁琐

SELECT 部门,'一月支出' 类别, t.一月支出 金额 FROM test1 t UNION ALL
SELECT 部门,'二月支出' 类别, t.二月支出 金额 FROM test1 t UNION ALL
SELECT 部门,'三月支出' 类别, t.三月支出 金额 FROM test1 t 
ORDER BY 部门,类别;

如果使用unpivot,一次扫描表test1即可完成，且语句更加简洁

SELECT 部门, 类别, 金额
  FROM test1 t UNPIVOT(金额 FOR 类别 IN(一月支出, 二月支出, 三月支出))
 ORDER BY 部门, 类别;