Search code examples
sqloracleunpivot

How to move 2 cols 1 row into 1 col 2 rows in Oracle


I have a query which I cannot seem to get right. I want to change the result set so it is 1 column and can be N number of rows.

Original query

select src_approval, dst_approval
from example_table
where id = 62615
group by src_approval, dst_approval

I tried the following which is not correct.

select src_approval, dst_approval
from example_table
unpivot
( colvalue for col in (src_approval, dst_approval) )
where id = 62615
group by src_approval, dst_approval

I tried the following which still returns 2 columns:

select *
from   
    (select src_approval, dst_approval
     from example_table
     where id= 62615
     group by src_approval, dst_approval)
unpivot
    (income_component_value
         for income_component_type in (src_approval, dst_approval)
       )

RESULT

select *
from   ( 
select src_approval, dst_approval
from e_p
where exemption_id = 62615
group by src_approval, dst_approval
)
 unpivot
       ( owner
         for approval in (src_approval, dst_approval)
       )

Solution

  • Yes, you can still use unpivot as

    select colvalue from 
    (
    select id, colvalue, col
      from example_table   
      unpivot(colvalue for col in(src_approval,dst_approval))
    )  
    where id = 62615
    

    Demo