Oracle学习笔记—-行转列PIVOT和列转行UNPIVOT

在做数据统计的时候，行转列，列转行是经常碰到的问题。case when、decode方式太麻烦了，而且可扩展性不强，可以使用 PIVOT，UNPIVOT比较快速实现行转列，列转行，而且可扩展性强

注意是否为空函数的使用nvl（）

函数语法：

select ...
from ...
pivot [xml]
(
    pivot_clause
    pivot_for_clause 
    pivot_in_clause 
)
where ...

一、行转列

1、测试数据准备

CREATE  TABLE StudentScores
(
   UserName         NVARCHAR2(20),        --学生姓名
   Subject          NVARCHAR2(30),        --科目
   Score            FLOAT              --成绩
)

INSERT INTO StudentScores SELECT '张三', '语文', 80 from dual;
INSERT INTO StudentScores SELECT '张三', '数学', 90 from dual;
INSERT INTO StudentScores SELECT '张三', '英语', 70 from dual;
INSERT INTO StudentScores SELECT '张三', '生物', 85 from dual;
INSERT INTO StudentScores SELECT '李四', '语文', 80 from dual;
INSERT INTO StudentScores SELECT '李四', '数学', 92 from dual;
INSERT INTO StudentScores SELECT '李四', '英语', 76 from dual;
INSERT INTO StudentScores SELECT '李四', '生物', 88 from dual;
INSERT INTO StudentScores SELECT '码农', '语文', 60 from dual;
INSERT INTO StudentScores SELECT '码农', '数学', 82 from dual;
INSERT INTO StudentScores SELECT '码农', '英语', 96 from dual;
INSERT INTO StudentScores SELECT '码农', '生物', 78 from dual;

2、行转列sql

SELECT * FROM 
    (SELECT * FROM StudentScores where 1=1) /*数据源*/
PIVOT 
(
    SUM(Score) AS bak   /*行转列后 列的值*/ 
    /*SUM(Score) AS bak, sum(score1) as bak1   可以为两个参数*/
    FOR p.Subject   /*需要行转列的列*/ 
    IN ('语文' as 语文,'数学' as 数学,'英语' as 英语,'生物' as 生物)  /*列的值*/
)

1、ORA-00904 subject标识符无效

select subject from 
    (select * from StudentScores where 1=1)
PIVOT 
(
    SUM(Score) AS bak 
    FOR p.Subject   
    IN ('语文' as 语文,'数学' as 数学,'英语' as 英语,'生物' as 生物) 
)
########################################
# 改sql返回的是转换之后的列。而不是原始列，经pivot转换后已经不存在subject。

2、pivot子句只标记要汇总的列（Score）及要转换的列（Subject）。语句中没有group by 子句。而是把子查询【数据源】中剩余的列统统放在group by中。如果其他的不动，只是在子查询【数据源】中增加列，则新增的列会自动放入group by中。

3、pivot子句返回的汇总列名由两部分组成。分别为'语文' as 语文 + Sum(score) as bak中的语文_bak构成。中间用下划线连接。

4、pivot实际上还是case when，便于维护，但不提高执行效率

执行结果：

二、列转行

1、测试数据准备

CREATE TABLE ProgrectDetail
(
    ProgrectName         NVARCHAR(20), --工程名称
    OverseaSupply        INT,          --海外供应商供给数量
    NativeSupply         INT,          --国内供应商供给数量
    SouthSupply          INT,          --南方供应商供给数量
    NorthSupply          INT           --北方供应商供给数量
)

INSERT INTO ProgrectDetail
SELECT 'A', 100, 200, 50, 50
UNION ALL
SELECT 'B', 200, 300, 150, 150
UNION ALL
SELECT 'C', 159, 400, 20, 320
UNION ALL

2、列转行的sql

SELECT P.ProgrectName,P.Supplier,P.SupplyNum
FROM 
(
    SELECT ProgrectName, OverseaSupply, NativeSupply,
           SouthSupply, NorthSupply
     FROM ProgrectDetail
)T
UNPIVOT 
(
    SupplyNum FOR Supplier IN
    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

执行结果：