9 Replies Latest reply on Mar 3, 2009 10:11 AM by bala1

# Row to cols

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
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
Hello,

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

Using Pivot functions

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