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.19
01-APR-10 col_2 5213.35
01-APR-10 col_3 2625.88
...
01-DEC-10 col_4 3748.64
01-DEC-10 col_5 13186.8
45 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.19
01-APR-10 COL_2 5213.35
...
01-DEC-10 COL_3 2371.24
01-DEC-10 COL_4 3748.64
01-DEC-10 COL_5 13186.8
45 rows selected.