CODEWITHSUNDEEP
sqloracle-databaseselectunpivot
djc55
djc55

Reputation: 555

Unpivoting into multiple columns in Oracle

I have a table as follows:

EVENT_ID    NUM_A    NUM_A_SRC    NUM_B    NUM_B_SRC
1           5        Yelp         4        Google
2           3        Yelp         1        Google

And I need to get it into the following format:

EVENT_ID    SRC       RANK
1           Yelp      5
1           Google    4
2           Yelp      3
2           Google    1

I cannot for the life of me figure out how to unpivot and have the result set be multiple columns. Can anyone help? Please see below for reproducible example:

CREATE TABLE EXAMPLE(
EVENT_ID NUMBER,
NUM_A NUMBER,
NUM_A_SRC VARCHAR2(10),
NUM_B NUMBER,
NUM_B_SRC VARCHAR2(10)
); 

INSERT INTO EXAMPLE (EVENT_ID, NUM_A, NUM_A_SRC, NUM_B, NUM_B_SRC)
VALUES
(1, 5, 'Yelp', 4, 'Google');

INSERT INTO EXAMPLE (EVENT_ID, NUM_A, NUM_A_SRC, NUM_B, NUM_B_SRC)
VALUES
(2, 3, 'Yelp', 1, 'Google');

SELECT * FROM EXAMPLE;

Upvotes: 0

Views: 70

Answers (2)

Amir Kadyrov
Amir Kadyrov

Reputation: 1288

One scan over the source table:

with s (event_id, num_a, num_a_src, num_b, num_b_src) as (
select 1, 5, 'Yelp', 4, 'Google' from dual union all
select 2, 3, 'Yelp', 1, 'Google' from dual)
select event_id,
decode(rn, 1, num_a    , 2, num_b    ) rank,
decode(rn, 1, num_a_src, 2, num_b_src) num_src
from s, (select rownum rn from dual connect by level <= 2)
order by rank desc;

  EVENT_ID       RANK NUM_SRC
---------- ---------- -------
         1          5 Yelp
         1          4 Google
         2          3 Yelp
         2          1 Google

Upvotes: 0

GMB
GMB

Reputation: 222462

In Oracle, the simplest approach is probably union all:

select event_id, num_a_src src, num_a rnk from example
union all
select event_id, num_b_src, num_b from example

Note that rank is a language keyword (there is a window function that goes by that name) - I renamed it to rnk in the query.

Demo on DB Fiddle:

EVENT_ID | SRC    | RNK
-------: | :----- | --:
       1 | Yelp   |   5
       2 | Yelp   |   3
       1 | Google |   4
       2 | Google |   1

Upvotes: 1

Related Questions