sql - 行到列 - Oracle - 不使用 Union ALL

Question

除了使用 union all 之外，还有什么更好的方法可以达到以下结果？
该表有数百万条记录，因此要寻找一个更好的选择，即一次获取结果集。

create table test_tab (
  rec_id number(3),
  p_code varchar2(5),
  q_code varchar2(5),
  r_code varchar2(5),
  p_amt number(8),
  q_amt number(8),
  r_amt number(8)
);

delete from test_tab;
insert into test_tab (rec_id, p_code, q_code,r_code , p_amt,q_amt,r_amt)
            values (1, 'p1','q1','r1',18,9,9);
insert into test_tab (rec_id, p_code, q_code,r_code , p_amt,q_amt,r_amt)
            values (2, 'p2','q2','r2',28,6,4);
insert into test_tab (rec_id, p_code, q_code,r_code , p_amt,q_amt,r_amt)
            values (3, 'p1',null,null,18,null,null);
insert into test_tab (rec_id, p_code, q_code,r_code , p_amt,q_amt,r_amt)
            values (4, null,'q3','r3',null,9,9);
commit;

select rec_id, p_code,p_amt from test_tab where p_code is not null
union all 
select rec_id, q_code,q_amt from test_tab where q_code is not null
union all 
select rec_id, r_code,r_amt from test_tab where r_code is not null;

结果:


REC_ID
代码
P_AMT


1
q1
9

1
p1
18

1
r1
9

2
p2
28

2
q2
6

2
r2
4

3
p1
18

4
q3
9

4
r3
9

Answer 1

这是unpivot的基本应用运算符，自 Oracle 11.1 起可用。

select   rec_id, code, amt
from     test_tab
unpivot  ((code, amt) for ord in 
               ((p_code, p_amt) as 1, (q_code, q_amt) as 2, (r_code, r_amt) as 3))
order by rec_id, ord   --  if needed
;


    REC_ID CODE         AMT
---------- ----- ----------
         1 p1            18
         1 q1             9
         1 r1             9
         2 p2            28
         2 q2             6
         2 r2             4
         3 p1            18
         4 q3             9
         4 r3             9

9 rows selected. 

注意一些事情。我调用输出列 code和 amt - 前缀 p_ 毫无意义在输出列名称中。此外，“排除空值”是 unpivot 中的默认值，所以我不需要明确提及它(虽然它不会伤害任何东西)。最后，虽然可能并不重要，但我还创建了一个专栏 ord以反射(reflect)列顺序，并按照与输入中的列相同的顺序对输出中的行进行排序。