2023年5月23日随笔
最近在学习Oracle数据库,涉及到行转列及列转行问题做个记录
原理:把某一个字段的值作为唯一值,然后另外一个字段的行值转换成它的列值。
一、行转列,UNPIVOT
1、查询原始数据表
select * from hadoop.ZB_OTC线下
查询结果如图所示
2、使用UNPIVOT
SELECT 年份,中心,事业部,产品编码,产品名称,品类,"全年金额(元)" as 全年指标,月份,指标金额 from hadoop.ZB_OTC线下
UNPIVOT (指标金额 FOR 月份 IN ("1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"))
运行结果如图所示
一、列转行,PIVOT
1、原始数据表运行
select * from HADOOP.SYB_DACHENG_WEEK表
运行结果
2、使用PIVOT运行
select * from
(select "营销中心","事业部名称","产品编码","产品名称","产品品类","年",标准金额,月 from HADOOP.SYB_DACHENG_WEEK表)
PIVOT(
SUM(标准金额) FOR 月 IN (
'1' AS "1月",'2' AS "2月",'3' AS "3月",'4' AS "4月",'5' AS "5月",'6' AS "6月",'7' AS "7月",'8' AS "8月",'9' AS "9月",'10' AS "10月",'11' AS "11月",'12' AS "12月")
)
运行结果