Oracle中使用Unpivot行转列后新增季度、年份独立字段的实现方法

实现思路 #

• 原有Unpivot逻辑已经正确完成行转列，仅需要在最终查询时对固定格式的Quarter_Year字段做字符串拆分即可
• 采用SUBSTR+INSTR组合实现字符串截取，兼容所有Oracle版本，不需要依赖高版本新特性
• 同步修正原有SQL中UNPIVOT的IN列表末尾多余的逗号语法问题

修改后完整SQL #

with a (
  Cost_Category,
  Cost_Category_Type,
  Q1_2020,
  Q2_2020,
  Q3_2020,
  Q4_2020
) as (
  select
    'Employee Impacted',
    'Period Cost',
    ATTRIBUTE_34,
    ATTRIBUTE_35,
    ATTRIBUTE_36,
    ATTRIBUTE_37
  from view_form_539766
)
select 
  Cost_Category,
  Cost_Category_Type,
  Quarter_Year,
  -- 拆分季度：取横杠前的内容
  substr(Quarter_Year, 1, instr(Quarter_Year, '-') - 1) as Quarter,
  -- 拆分年份：取横杠后的内容
  substr(Quarter_Year, instr(Quarter_Year, '-') + 1) as Year,
  value
from a
unpivot (
  value
  for Quarter_Year in (
    q1_2020 as 'Q1-2020',
    q2_2020 as 'Q2-2020',
    q3_2020 as 'Q3-2020',
    q4_2020 as 'Q4-2020'
  )
)

效果说明 #

以你给出的示例数据为例，执行后会得到如下结构的结果：

内容的提问来源于stack exchange，提问作者Gaurav Kanodia