sql - 行到列 - Oracle - 不使用联合 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_CODE	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

标签： sqloracle

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来反映列顺序，并按照与输入中的列相同的顺序对输出中的行进行排序。