Conversion of Oracle Columns into Rows

You Asked

Hi Tom, 

I have t1 table as follows 
create table t1 
(mnth varchar2(10), 
col_1 number, 
col_2 number, 
col_3 number, 
col_4 number, 
col_5 number 
); 
insert into t1 values ('APR-2010',2505.19,5213.35,2625.88,2060.94,11167.73); 
insert into t1 values ('MAY-2010',3334.27,1889.67,3469.36,3269.67,11460.28); 
insert into t1 values ('JUN-2010',3215.31,2391.78,1245.07,4830.8,11469.88); 
insert into t1 values ('JUL-2010',3361.81,2362.52,1287.19,4189.05,10800.89); 
insert into t1 values ('AUG-2010',3456.76,2345.95,1767.83,3394.97,11669.05); 
insert into t1 values ('SEP-2010',4331.62,2881.55,1582.57,2745.13,12285.43); 
insert into t1 values ('OCT-2010',3685.12,2845.24,2033.88,2925.94,12308.12); 
insert into t1 values ('NOV-2010',3152.47,2685.41,2042.69,2734.54,12556.45); 
insert into t1 values ('DEC-2010',989.35,3126.18,2371.24,3748.64,13186.8); 

Select * from t1; 

Month COL_1 COL_2 COL_3 COL_4 COL_5 
1 APR-2010 2505.19 5213.35 2625.88 2060.94 11167.73 
2 MAY-2010 3334.27 1889.67 3469.36 3269.67 11460.28 
3 JUN-2010 3215.31 2391.78 1245.07 4830.8 11469.88 
4 JUL-2010 3361.81 2362.52 1287.19 4189.05 10800.89 
5 AUG-2010 3456.76 2345.95 1767.83 3394.97 11669.05 
6 SEP-2010 4331.62 2881.55 1582.57 2745.13 12285.43 
7 OCT-2010 3685.12 2845.24 2033.88 2925.94 12308.12 
8 NOV-2010 3152.47 2685.41 2042.69 2734.54 12556.45 
9 DEC-2010 989.35 3126.18 2371.24 3748.64 13186.8 

I need result as follows through Query 
Month COL Value 
APR-2010 COL_1 2505.19 
APR-2010 COL_2 5213.35 
APR-2010 COL_3 2625.88 
APR-2010 COL_4 2060.94 
APR-2010 COL_5 11167.73 
. . . 
. . . 
. . . 
. . . 
Please Guide. 
Mangesh Malkar 

and we said...

ugh, I had it when people use a string to store a date. I've fixed that for you - you should do it too. 

ops$tkyte%ORA11GR2> create table t1  2  (mnth date constraint check_mnth check (mnth = trunc(mnth,'mm')),  3  col_1 number,  4  col_2 number,  5  col_3 number,  6  col_4 number,  7  col_5 number  8  );Table created.ops$tkyte%ORA11GR2> insert into t1 values (to_date('APR-2010','mon-yyyy'),2505.19,5213.35,2625.88,2060.94,11167.73);.....

that is the data... 

then in 10g you can: 

ops$tkyte%ORA11GR2> with data as (select level l from dual connect by level <= 5)  2  select mnth, 'col_' || l, decode( l, 1, col_1, 2, col_2, 3, col_3, 4, col_4, 5, col_5 ) col  3    from data, t1  4   order by mnth, l  5  /MNTH      'COL_'||L                                           COL--------- -------------------------------------------- ----------01-APR-10 col_1                                           2505.1901-APR-10 col_2                                           5213.3501-APR-10 col_3                                           2625.88...01-DEC-10 col_4                                           3748.6401-DEC-10 col_5                                           13186.845 rows selected.

and in 11g that simplifies to 

ops$tkyte%ORA11GR2> SELECT * FROM t1  2    UNPIVOT (col FOR data IN (col_1, col_2, col_3, col_4, col_5))  3  /MNTH      DATA         COL--------- ----- ----------01-APR-10 COL_1    2505.1901-APR-10 COL_2    5213.35...01-DEC-10 COL_3    2371.2401-DEC-10 COL_4    3748.6401-DEC-10 COL_5    13186.845 rows selected.