在Oracle SQL中使用列名进行转换

Question

我在Oracle SQL中的pivot/unpivot方面做了一些工作，但这项工作似乎有点复杂，需要一些帮助。下面是我的表格设计

CompA_curr_yr_due_amt101>td>1001

年度	Comp_A_prev_yr_due_ct	Comp_Acurr_yr_due_ct
2019	100	1000

你在找这样的东西吗？

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;

db<上的演示>小提琴

我建议cross apply：横向连接是许多数据库支持的一个有用且高效的功能。在Oracle中(假设12c或更高(：

select t.year, x.*
from mytable t
cross apply (
select 
'A'                    as company, 
comp_a_prev_yr_due_ct  as due_ct, 
comp_a_prev_yr_due_amt as due_amt, 
'prev'                 as prev_or_cur 
from dual
union all
select 'A', comp_a_cur_yr_due_ct as due_ct, comp_a_cur_yr_due_amt, 'cur' from dual
) x