CODEWITHSUNDEEP
sqloracle-database
Martin
Martin

Reputation: 219

Transpose vertical input data to horizontal output in Oracle

I need to transpose rows to columns in Oracle. I've the data in this format: 

Apple Orange Mango Banana
15    20     12    67

The required result is:

Fruit      Number
Apple      15
Orange     20
Mango      12
Banana     67

I used Union to get this result but this is not the generic one. 

SELECT ‘Apple’ AS Fruit, Apple AS Number FROM fruits_tbl UNION
SELECT ‘Orange’, Orange FROM fruits_tbl UNION
SELECT ‘Mango’, Mango FROM fruits_tbl UNION
SELECT ‘Banana’, Banana FROM fruits_tbl;

I want standard procedure to get the output as suggested.

Update: Figured out Pivot is the correct approach!

Upvotes: 0

Views: 2188

Answers (1)

Multisync
Multisync

Reputation: 8797

Since Oracle 11g (tab is your table name):

select * from tab 
UNPIVOT (num for fruit in (apple as 'apple', orange as 'orange', mango as 'mango', banana as 'banana'));

Oracle 10g:

with col_names as (
select 'apple' fruit from dual
union all select 'orange' from dual
union all select 'mango' from dual
union all select 'banana' from dual
)
select c.fruit, 
       case c.fruit       
          when  'apple' then t.apple          
          when  'orange' then t.orange          
          when  'mango' then t.mango          
          when  'banana' then t.banana          
       end as num
from tab t
cross join col_names c;

Upvotes: 1

Related Questions