Oracle数据库SQL复杂查询、数据处理（五、PIVOT函数行列转换）

使用 PIVOT 和 UNPIVOT

PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列，来轮替表值表达式。 PIVOT 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。

案例：

SELECT FACTORYDATE AS DATE_TIME,
        FACTORYNAME,
        PRODUCTIONTYPE,
        SUM (SHIPMAINQTY) - SUM (UNSHIPMAINQTY) SHIPMAINQTY
FROM FPS_PRDPROD_DAY
WHERE FACTORYDATE BETWEEN '20190701' AND '20190711'
AND PRODUCTIONTYPE IN ('P','E')
AND FACTORYNAME = 'ARRAY'  
GROUP BY FACTORYDATE,FACTORYNAME,PRODUCTIONTYPE
ORDER BY FACTORYDATE ASC

执行结果：

---

使用PIVOT函数后：

SELECT DATE_TIME,
       FACTORYNAME,
       NVL(P_QTY,0) AS P_QTY,
       NVL(E_QTY,0) AS E_QTY
FROM 
(
SELECT FACTORYDATE AS DATE_TIME,
        FACTORYNAME,
        PRODUCTIONTYPE,
        SUM (SHIPMAINQTY) - SUM (UNSHIPMAINQTY) SHIPMAINQTY
FROM FPS_PRDPROD_DAY
WHERE FACTORYDATE BETWEEN '20190701' AND '20190711'
AND PRODUCTIONTYPE IN ('P','E')
AND FACTORYNAME = 'ARRAY'  
GROUP BY FACTORYDATE,FACTORYNAME,PRODUCTIONTYPE
ORDER BY FACTORYDATE ASC
)
PIVOT (SUM(SHIPMAINQTY) FOR PRODUCTIONTYPE IN ('P' P_QTY,'E' E_QTY))

执行结果：

详细用法：
https://blog.youkuaiyun.com/software_kid/article/details/49487773