I have a table - Base_table

create table base_table (ID number,FACTOR_1 number,FACTOR_1 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');

I need to insert this data into another table (FCT_T) based on certain criterias.

Also, I am trying to avaoid usage of unpivot as there are several other columns that I need to insert and manage as part of insert.

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

Logic to use -

Below values are not part of table, but needs to be used (hard-coded) in logic/criteria (perhaps CASE statements) -

What I need is something similar (or any better approach available) -

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

So far not able to figure out, how I can move factor column as rows, given an ID can have any number of rows from 1 to 4 based on criteria. Appreciate help here.

Partial final/expected output (FCT_T) -

CodePudding user response：

You can join the table to your codes and then UNPIVOT to convert columns into rows:

INSERT INTO fct_t (id, p_code, p_value)
WITH codes (M_VAL, FACT_1_CODE, FACT_2_CODE, FACT_3_CODE, FACT_4_CODE) AS (
  SELECT 'M1', 'R1',  'R2',  'R3',   'R4'   FROM DUAL UNION ALL
  SELECT 'M2', 'R21', 'R65', 'R6',   'R245' FROM DUAL UNION ALL
  SELECT 'M3', 'R1',  'R01', 'R212', 'R365' FROM DUAL
)
SELECT id, p_code, p_value
FROM   base_table b
       INNER JOIN codes c
       ON (b.j_code = c.m_val)
UNPIVOT (
  (p_code, p_value)
  FOR factor IN (
    (fact_1_code, factor_1) AS 1,
    (fact_2_code, factor_2) AS 2,
    (fact_3_code, factor_3) AS 3,
    (fact_4_code, factor_4) AS 4
  )
)
WHERE p_value IS NOT NULL;

db<>fiddle here