This discussion is archived
9 Replies Latest reply: Mar 3, 2009 2:11 AM by bala1 RSS

Row to cols

bala1 Newbie
Currently Being Moderated
Hi all,

my requirement is to convert the row into cols

The i/p is
  col1     col2  col3 col4 col5
*****    ****  **** **** ****
saturday 30    50   75   80 
sunday   25    67   56   10
monday   25    10   34   67

the o/p should be

saturday  sunday monday
30          25     25
50          67     10
75          56     34
80          10     67
I am using the oracle 11g database.
  • 1. Re: Row to cols
    OrionNet Guru
    Currently Being Moderated
    Hello,

    There is pivot function in 11g but here are some useful links.
    Also search forum for rows to column and you will tons of examples.

    http://mennan.kagitkalem.com/ConvertingRowsToColumnsInOracle.aspx
    http://www.dba-oracle.com/t_converting_rows_columns.htm


    Regards
  • 2. Re: Row to cols
    OrionNet Guru
    Currently Being Moderated
    Hello,

    Here is examples of using pivot function in 11g might be helpful to you.

    Using Pivot functions

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFIIDD

    Regards
  • 3. Re: Row to cols
    bala1 Newbie
    Currently Being Moderated
    I am new to work with using the PIVOT function.. can we use some other approach to do this task?
  • 4. Re: Row to cols
    21205 Oracle ACE Director
    Currently Being Moderated
    I am new to work with using the PIVOT function.. can we use some other approach to do this task?
    So? Take the opportunity to learn something new.
  • 5. Re: Row to cols
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    I have used "model clause" ;-)
    with t as(
    select 'sat' as key,30 Val1,50 Val2,75 Val3,80 Val4 from dual union
    select 'sun',25,67,56,10 from dual union
    select 'mon',25,10,34,67 from dual)
    select Val1,Val2,Val3
      from t
     model RETURN UPDATED ROWS
    dimension by(key)
    measures(Val1,Val2,Val3,Val4)
    rules(
    Val1['A'] = Val1['sat'], Val2['A'] = Val1['sun'], Val3['A'] = Val1['mon'],
    Val1['B'] = Val2['sat'], Val2['B'] = Val2['sun'], Val3['B'] = Val2['mon'],
    Val1['C'] = Val3['sat'], Val2['C'] = Val3['sun'], Val3['C'] = Val3['mon'],
    Val1['D'] = Val4['sat'], Val2['D'] = Val4['sun'], Val3['D'] = Val4['mon'])
    order by key;
    
    Val1  Val2  Val3
    ----  ----  ----
      30    25    25
      50    67    10
      75    56    34
      80    10    67
  • 6. Re: Row to cols
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    I have used "unpivot" and "pivot" B-)
    with t as(
    select 'sat' as ID,30 Val1,50 Val2,75 Val3,80 Val4 from dual union
    select 'sun',25,67,56,10 from dual union
    select 'mon',25,10,34,67 from dual)
    select * from t
    unpivot(vals for key in(Val1,Val2,Val3,Val4))
    pivot (max(VALS) for ID in('sat' as sat,'sun' as sun,'mon' as mon))
    order by Key;
    
    KEY   sat  sun  mon
    ----  ---  ---  ---
    Val1   30   25   25
    Val2   50   67   10
    Val3   75   56   34
    Val4   80   10   67
  • 7. Re: Row to cols
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    I have used Old solution :8}
    with t as(
    select 'sat' as key,30 Val1,50 Val2,75 Val3,80 Val4 from dual union
    select 'sun',25,67,56,10 from dual union
    select 'mon',25,10,34,67 from dual)
    select max(decode(key,'sat',Val)) as sat,
           max(decode(key,'sun',Val)) as sun,
           max(decode(key,'mon',Val)) as mon
    from (select t.key,b.column_Value,
          case b.column_Value when 1 then Val1
                              when 2 then Val2
                              when 3 then Val3
                              when 4 then Val4 end as Val
           from t,table(sys.odciNumberList(1,2,3,4)) b)
    group by column_Value
    order by column_Value;
  • 8. Re: Row to cols
    21205 Oracle ACE Director
    Currently Being Moderated
    Aketi Jyuuzou wrote:
    I have used Old solution :8}
    You're a man of many trades ;-)
  • 9. Re: Row to cols
    bala1 Newbie
    Currently Being Moderated
    Hi,

    I tried the below query. It gives the desired ouput... But in case if the table contains more than 50 cols. so everytime we can use to mention the column name in the pivot and then to validate it. As per that scenario, This query becomes too long... is it possible to write to simplify this as query below?
    select * from (select col1,col2
                   from day)
    pivot (max(col2) for col1 in ('monday','saturday','sunday'))
    union all
    select * from (select col1,COL3
                   from day)
    pivot (max(col3) for col1 in ('monday','saturday','sunday'))
    union all
    select * from (select col1,COL4
                   from day)
    pivot (max(col4) for col1 in ('monday','saturday','sunday'))
    union all
    select * from (select col1,COL5
                   from day)
    pivot (max(col5) for col1 in ('monday','saturday','sunday'))

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points