9 Replies Latest reply: Mar 3, 2009 4:11 AM by bala1 RSS

    Row to cols

    bala1
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Aketi Jyuuzou wrote:
                        I have used Old solution :8}
                        You're a man of many trades ;-)
                        • 9. Re: Row to cols
                          bala1
                          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'))