insert records into a table - column as rows based on conditions

user_13915313 0
Viewed 16

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');
ID FACTOR_1 FACTOR_2 FACTOR_3 FACTOR_4 TOTAL J_CODE
1 10 52 5 32 140 M1
2 null 32 24 12 311 M2
3 12 null 53 null 110 M3
4 43 45 42 3 133 M1
5 432 24 null 68 581 M2
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) -

M_VAL FACT_1_CODE FACT_2_CODE FACT_3_CODE FACT_4_CODE
M1 R1 R2 R3 R4
M2 R21 R65 R6 R245
M3 R1 R01 R212 R365

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) -

ID P_CODE P_VALUE
1 R1 10
1 R2 52
1 R3 5
1 R4 32
2 R65 32
2 R6 24
2 R245 12
oracle19c oracle
1 Answers

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

user_15092640
Related