By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
create table test(year int,Comp_A_prev_yr_due_ct int,Comp_A_prev_yr_due_amt int,
Comp_A_curr_yr_due_ct int,Comp_A_Curr_yr_due_amt int,Comp_B_Curr_yr_due_amt int
);
insert into test values(2019,100,1000,101,1001,300);
1 rows affected
insert into test values(2020,200,2000,201,2001,340);
1 rows affected
select * from test
| YEAR | COMP_A_PREV_YR_DUE_CT | COMP_A_PREV_YR_DUE_AMT | COMP_A_CURR_YR_DUE_CT | COMP_A_CURR_YR_DUE_AMT | COMP_B_CURR_YR_DUE_AMT |
|---|---|---|---|---|---|
| 2019 | 100 | 1000 | 101 | 1001 | 300 |
| 2020 | 200 | 2000 | 201 | 2001 | 340 |
SELECT regexp_substr(company_prev_curr, '(.*?_){1}(.*?)_', 1, 1, '', 2) AS COMPANY
,year
,due_ct
,due_amt
,regexp_substr(company_prev_curr, '(.*?_){2}(.*?)_', 1, 1, '', 2) AS PREV_CURR
FROM test
unpivot(
(due_ct,due_amt)
FOR company_prev_curr IN (
(Comp_A_prev_yr_due_ct,Comp_A_prev_yr_due_amt),
(Comp_A_curr_yr_due_ct,Comp_A_Curr_yr_due_amt)
)
) u;
| COMPANY | YEAR | DUE_CT | DUE_AMT | PREV_CURR |
|---|---|---|---|---|
| A | 2019 | 100 | 1000 | PREV |
| A | 2019 | 101 | 1001 | CURR |
| A | 2020 | 200 | 2000 | PREV |
| A | 2020 | 201 | 2001 | CURR |