CODEWITHSUNDEEP
sqloracle-database
Renjith V
Renjith V

Reputation: 21

Rows to Columns - Oracle - Not using Union ALL

Is there any better way achieve the below result other than using union all? The table has millions of records, so looking for a better option where the result set is fetched once.

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;

Result:

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

Upvotes: 2

Views: 136

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a lateral join:

select x.*
from test_tab t cross join lateral
     (select t.rec_id, t.p_code as code, t.p_amount as amount from dual union all
      select t.rec_id, t.q_code, t.q_amount from dual union all
      select t.rec_id, t.r_code, t.r_amount from dual
     ) x
where code is not null;

Technical, this still has a union all, but it is only scanning the original table once.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use Hiearchy query and cross join as follows:

select * from (select rec_id, 
       case lvl when 1 then p_code when 2 then q_code else r_code end as p_code,
       case lvl when 1 then p_amt when 2 then q_amt else r_amt end as p_amount
  from test_tab 
cross join (select level as lvl from dual connect by level <= 3) )
where p_code is not null

Upvotes: 0

user5683823
user5683823

Reputation:

This is a basic application of the unpivot operator, available since 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.

Notice a few things. I call the output columns code and amt - it makes no sense to have the prefix p_ in the output column names. Also, "exclude nulls" is the default in unpivot, so I didn't need to mention it explicitly (although it wouldn't hurt anything). Finally, while perhaps not critical, I also created a column ord to reflect column order, and ordered the rows in the output in the same order as you had the columns in the input.

Upvotes: 2

Related Questions