how is possible to rotate data in a sql table?

Question

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

If you look carefully, the specified duplicate is not really a duplicate. — Gordon Linoff, Sep 24 '17 at 12:30

score 4 · Accepted Answer · answered Sep 24 '17 at 12:12

4

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;

answered Sep 24 '17 at 12:12

Gordon Linoff

1,242,037
58
646
786

Thankyou very much – Saeid Sep 24 '17 at 12:25
Scanning original table 5 times? You cant be serious. :) – Dr Y Wit Sep 24 '17 at 22:39
@DrYWit . . . I'm guessing it is a pretty small table. – Gordon Linoff Sep 25 '17 at 01:26

score 0 · Answer 2 · answered Sep 24 '17 at 22:52

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

how is possible to rotate data in a sql table?

2 Answers2