Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Mangesh.

Asked: December 21, 2010 - 6:35 am UTC

Last updated: December 27, 2010 - 3:50 pm UTC

Version: 10.2.0.4.0

Viewed 10K+ times! This question is

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 Tom 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.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

a string to store a date

Sokrates, December 27, 2010 - 1:00 pm UTC

hi tom

why is first_load_time in v$sql and v$sqlarea a varchar2 ?
and not a date ?

Thanks
Tom Kyte
December 27, 2010 - 3:50 pm UTC

because not everyone gets it, even people that work at this company.

They should obviously be dates, I frequently have to to_date them to do anything sensible with them short of reporting on them.

ok thanks

Sokrates, December 28, 2010 - 2:23 am UTC