ORACLE unpivot columns to rows

问题:

ORACLE 10 Hi, I was reading several posts here and i did not come up to a simple solution

I have this data:

Transacion_ID  GROSS_AMOUNT  DISCOUNT_AMOUNT 
1,             10 ,          -1 
2,             1002 ,        -14 
3,             36 ,          -5 

And I need to unpivot to get

Transacion_ID  TYPE     AMOUNT 
1,             GROSS ,  10 
1,             DISC  ,  -1 
2,             GROSS ,  1002 
2,             DISC  ,  -14 
3,             GROSS ,  36 
3,             DISC  ,  -5 

My first approach was to split this in two queries and then just UNION ALL boths results, but this run every 5 hours and has several joins, so spliting means nearly duplicate exec time. I was looking for something like pivot/unpivot in oracle 10.

回答1:

Try this

Select * From (
Select 
Transacion_ID,
Case When C.lvl = 1 Then 'GROSS'
     When C.lvl = 2 Then 'DISC'
End TYPE,
Case When C.lvl = 1 Then GROSS_AMOUNT
     When C.lvl = 2 Then DISCOUNT_AMOUNT
End AMOUNT
From T
cross join (select level lvl from dual connect by level<=2) c     
) where amount is not null
order by 1

SQL DEMO

This query is based on method mentioned here