【实例简介】
【实例截图】
【核心代码】
SELECT * FROM (SELECT 'a1' AS A1, 'a2' AS A2, 'a3' AS A3, 'a4' AS A4 FROM DUAL) T
UNPIVOT
INCLUDE NULLS --INCLUDE NULLS --包含控空值
(
(ROW1) --自定义列名称
FOR COL1 --自定义列名称
IN(A1,A2,A3,A4 ) --要转置的列名
)
--多列
SELECT * FROM (SELECT 'm1' AS M1,'a1' AS A1,'m2' AS M2, 'a2' AS A2,'m3' AS M3, 'a3' AS A3,'m4' AS M4, 'a4' AS A4 FROM DUAL) T
UNPIVOT
INCLUDE NULLS --INCLUDE NULLS --包含控空值
(
(ROW1,ROW2) --自定义列名称
FOR COL1 --自定义列名称
IN((m1,a1)as 'ma1',(m2,a2)as 'ma2',(m3,a3)as 'ma3',(m4,a4)as 'ma4' ) --要转置的列名
)
--统计水果在每个月的销量
select * from(
select '201812' AS MONTH_ID,'苹果' AS A1,300 AS SAL FROM DUAL
UNION ALL
select '201811' AS MONTH_ID,'香蕉' AS A1,400 AS SAL FROM DUAL
UNION ALL
select '201811' AS MONTH_ID,'苹果' AS A1,200 AS SAL FROM DUAL
UNION ALL
select '201812' AS MONTH_ID,'香蕉' AS A1,500 AS SAL FROM DUAL
) t
PIVOT
(SUM(SAL) --统计销量 字段
FOR MONTH_ID --字段名
IN ('201811' as 201811,'201812' as 201812) --字段的值
)
--统计每个月份的水果销量
select * from(
select '201812' AS MONTH_ID,'苹果' AS A1,300 AS SAL FROM DUAL
UNION ALL
select '201811' AS MONTH_ID,'香蕉' AS A1,400 AS SAL FROM DUAL
UNION ALL
select '201811' AS MONTH_ID,'苹果' AS A1,200 AS SAL FROM DUAL
UNION ALL
select '201812' AS MONTH_ID,'香蕉' AS A1,500 AS SAL FROM DUAL
) t
PIVOT
(SUM(SAL) --统计销量 字段
FOR (a1) --字段名
IN ( '香蕉' ,'苹果' ) --字段的值
)
网友评论
我要评论