CODEWITHSUNDEEP
sqloracle-database
Ganesh
Ganesh

Reputation: 506

SQL Query to Unpivot dependent column using oracle

i am trying to replicate below scenario using oracle sql query:

enter image description here

i have did it using union query , but in that query i am hitting same table multiple times, is there any alternate for this query?

Please note: col and val are depenedent on each other simillarly col_1 and val_1 are also dependent on each other

select id,col, val from tbl
union
select id,col_1, val_1 from tbl

Upvotes: 0

Views: 62

Answers (2)

user5683823
user5683823

Reputation:

You can pivot and unpivot multiple columns with the PIVOT and UNPIVOT operators, you just need to know the correct syntax. In your case you want to UNPIVOT. You are losing some information in the way you show the desired output, and perhaps that's OK for your needs; in any case, below I include a column that shows the order of the pairs of columns. (A bit odd, showing 1 for COL/VAL and 2 for COL_1/VAL_1, but I assume those aren't your real column names anyway.) If you don't need the ORD column in the output, just drop it from the SELECT clause.

Note - COLUMN is an Oracle reserved word, it can't be a column name. I changed the column names to C and V in the output.

with
  input(id, col, col_1, val, val_1) as (
    select 1, 'ABC', 'DEF', 10, 20 from dual union all
    select 2, 'GHI', 'JKL', 30, 40 from dual
  )
select  id, ord, c, v
from    input
unpivot ( (c, v) for ord in ((col, val) as 1, (col_1, val_1) as 2))
;

        ID        ORD C            V
---------- ---------- --- ----------
         1          1 ABC         10
         1          2 DEF         20
         2          1 GHI         30
         2          2 JKL         40

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Oracle 12C+ supports lateral joins, so you can do:

select t.id, v.col, v.val
from tbl t cross join lateral
     (select t.col, t.val from dual union all
      select t.col_1, t.val from dual
     ) v;

Upvotes: 1

Related Questions