CODEWITHSUNDEEP
sqloracle-database
Saeid
Saeid

Reputation: 448

how is possible to rotate data in a sql table?

i have a an oracle database table that contains these info :

name      jan    feb    march   april   may
item1     15      10     45       19    80
item2     13      16     19       64    80

is it possible to write a query to have this new table ?

monthName         item1          item2
jan                 15            13
feb                 10            16
march               45            19 
april               19            64
may                 80            80

Upvotes: 1

Views: 691

Answers (2)

Dr Y Wit
Dr Y Wit

Reputation: 2020

11g+ (unpivot + pivot or other way round - pivot + unpivot)

select *
  from (select *
          from t
        unpivot (item for monthname in (jan as 'jan', feb as 'feb', march as 'march', april as 'april', may as 'may')))
 pivot (sum(item) for name in ('item1' as item1, 'item2' as item2))

Pre 11g (cross join + group by or group by + cross join)

select monthname,
       max(decode(name, 'item1', decode(monthname, 'jan', jan, 'feb', feb, 'march', march, 'april', april, 'may', may))) item1,
       max(decode(name, 'item2', decode(monthname, 'jan', jan, 'feb', feb, 'march', march, 'april', april, 'may', may))) item2
  from t
 cross join (select to_char(add_months(date '0001-01-01', rownum - 1),
                            case when rownum < 3 then 'fmmon' else 'fmmonth' end) monthname
               from dual connect by rownum <= 5)
 group by monthname

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

One way is union all with aggregation:

select monthname,
       max(case when name = 'item1' then item end) as item1,
       max(case when name = 'item2' then item end) as item2
from ((select 'jan' as monthname, name, jan as item from t) union all
      (select 'feb' as monthname, name, feb as item from t) union all
      (select 'mar' as monthname, name, mar as item from t) union all
      (select 'apr' as monthname, name, apr as item from t) union all
      (select 'may' as monthname, name, may as item from t)
     ) t
group by monthname;

Upvotes: 4

Related Questions