按条件将Base_table多列因子转成行插入FCT_T表（避免UNPIVOT）

现有Base_table业务表，建表语句及测试数据插入脚本如下：

create table base_table (
  ID number,
  FACTOR_1 number,
  FACTOR_2 number,
  FACTOR_3 number,
  FACTOR_4 number,
  TOTAL number, 
  J_CODE varchar2(10)
);

insert into base_table values (1,10,52,5,32,140,'M1');
insert into base_table values (2,null,32,24,12,311,'M2');
insert into base_table values (3,12,null,53,null,110,'M3');
insert into base_table values (4,43,45,42,3,133,'M1');
insert into base_table values (5,432,24,null,68,581,'M2');
insert into base_table values (6,null,7,98,null,196,'M1');

base_table表结构及测试数据预览：

需要将base_table的数据按规则插入目标表FCT_T，要求不使用UNPIVOT语法（插入流程需要维护多个其他字段，UNPIVOT实现复杂度高）。
目标表FCT_T建表语句：

create table fct_t (
  id number, 
  p_code varchar2(21), 
  p_value number
);

编码映射规则不存储物理表，需要硬编码实现（可使用CASE语句），映射关系如下：

转换规则：

• 仅保留因子值非空、且大于0的记录
• 每个符合条件的因子单独生成一行记录，单个ID最终会生成1-4条不等的记录

当前写法只能生成单条因子记录，无法实现列转行的效果：

insert into FCT_T values 
select id, 
case when FACTOR_1>0 and J_CODE = 'M1' then 'R1' end ,
factor_1
from base_table;

目标表FCT_T部分预期结果如下：

使用UNION ALL拼接4个因子的查询逻辑，每个查询单独处理对应因子的编码映射、非空和大于0判断，完全不需要使用UNPIVOT，也方便后续扩展其他字段逻辑：

INSERT INTO fct_t (id, p_code, p_value)
-- 处理FACTOR_1
SELECT 
  id,
  CASE J_CODE
    WHEN 'M1' THEN 'R1'
    WHEN 'M2' THEN 'R21'
    WHEN 'M3' THEN 'R1'
  END AS p_code,
  FACTOR_1 AS p_value
FROM base_table
WHERE FACTOR_1 IS NOT NULL AND FACTOR_1 > 0

UNION ALL
-- 处理FACTOR_2
SELECT 
  id,
  CASE J_CODE
    WHEN 'M1' THEN 'R2'
    WHEN 'M2' THEN 'R65'
    WHEN 'M3' THEN 'R01'
  END AS p_code,
  FACTOR_2 AS p_value
FROM base_table
WHERE FACTOR_2 IS NOT NULL AND FACTOR_2 > 0

UNION ALL
-- 处理FACTOR_3
SELECT 
  id,
  CASE J_CODE
    WHEN 'M1' THEN 'R3'
    WHEN 'M2' THEN 'R6'
    WHEN 'M3' THEN 'R212'
  END AS p_code,
  FACTOR_3 AS p_value
FROM base_table
WHERE FACTOR_3 IS NOT NULL AND FACTOR_3 > 0

UNION ALL
-- 处理FACTOR_4
SELECT 
  id,
  CASE J_CODE
    WHEN 'M1' THEN 'R4'
    WHEN 'M2' THEN 'R245'
    WHEN 'M3' THEN 'R365'
  END AS p_code,
  FACTOR_4 AS p_value
FROM base_table
WHERE FACTOR_4 IS NOT NULL AND FACTOR_4 > 0;

方案说明：

• 逻辑拆分清晰，每个因子的判断、映射规则独立维护，新增、修改字段逻辑时不需要改动其他部分
• 自动过滤空值、小于等于0的因子值，符合业务规则
• 完全规避UNPIVOT语法，可灵活在每个子查询中添加其他需要维护的字段逻辑
• 执行性能稳定，适合大数据量场景

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