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.csdn.net/software_kid/article/details/49487773