SQL & PL/SQL

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

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.

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

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

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;

You're a man of many trades ;-)

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'))