Oracle SQL基于日期字段的PIVOT行转列实现方法咨询

需求说明 #

现有原始数据如下：

转换规则：

• 按TYP字段分组
• 对VALUE字段求和作为汇总列
• 将ORDER_DATE的不同取值转为单独列，对应展示同分组下该日期的CNT值

最终预期结果：

实现代码 #

假设原始表名为original_table，可直接使用以下SQL实现：

SELECT *
FROM (
  -- 内层预处理：计算每个TYP对应的VALUE总求和
  SELECT 
    TYP,
    ORDER_DATE,
    CNT,
    SUM(VALUE) OVER (PARTITION BY TYP) AS VALUE
  FROM original_table
)
PIVOT (
  -- 行转列聚合：取每个日期对应的CNT值
  MAX(CNT)
  FOR ORDER_DATE IN (
    '27-OCT-2021' AS "27-OCT-2021",
    '28-OCT-2021' AS "28-OCT-2021"
  )
);

说明 #

• 内层子查询用窗口函数SUM(VALUE) OVER (PARTITION BY TYP)提前按TYP分组计算VALUE总和，避免行转列后重复聚合出现错误
• PIVOT子句中使用MAX(CNT)是因为每个TYP+ORDER_DATE组合唯一，用MIN、SUM等聚合函数也可正常取到对应CNT值
• FOR ORDER_DATE IN后可按需罗列需要转为列的日期取值，别名用双引号包裹可保留日期格式的列名。如果日期取值不固定，可结合动态SQL拼接IN后的取值列表实现动态行转列。

内容的提问来源于stack exchange，提问作者Imran Hemani