Re: thanks here is solution

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 19 Feb 1999 20:20:19 GMT
Message-ID: <36e0c759.27682154@192.86.155.100>

A copy of this was sent to jenny_at_aol.com (jennfier) (if that email address didn't require changing) On 19 Feb 1999 13:56:41 -0600, you wrote:

>here is the first table tab24row (I did 4 rows instead of 24 for simplicity)
>
>NAME READDATE READHOUR READING
>---------- ----------- --------- ---------
>system 19-feb-1999 1 1000
>system 19-feb-1999 2 1500
>system 19-feb-1999 3 2000
>system 19-feb-1999 4 1400
>

well, if you don't want to build that table every day, you could just create a view that will 'maintain' itself:

SQL> create table t
  2 ( name varchar2(25),
  3 readdate date,
  4 readhour int,
  5 reading int )
  6 /

Table created.

SQL>
SQL> begin

  2          for i in 1 .. 24 loop
  3                  insert into t values ( 'system', trunc(sysdate), i, i*25 );
  4          end loop;

  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace view t_view
  2 as
  3 select name, readdate,

  4  sum(decode(readhour, 1, reading, 0 )) reading1,
  5  sum(decode(readhour, 2, reading, 0 )) reading2,
  6  sum(decode(readhour, 3, reading, 0 )) reading3,
  7  sum(decode(readhour, 4, reading, 0 )) reading4,
  8  sum(decode(readhour, 5, reading, 0 )) reading5,
  9  sum(decode(readhour, 6, reading, 0 )) reading6,
 10  sum(decode(readhour, 7, reading, 0 )) reading7,
 11  sum(decode(readhour, 8, reading, 0 )) reading8,
 12  sum(decode(readhour, 9, reading, 0 )) reading9,
 13  sum(decode(readhour, 10, reading, 0 )) reading10,
 14  sum(decode(readhour, 11, reading, 0 )) reading11,
 15  sum(decode(readhour, 12, reading, 0 )) reading12,
 16  sum(decode(readhour, 13, reading, 0 )) reading13,
 17  sum(decode(readhour, 14, reading, 0 )) reading14,
 18  sum(decode(readhour, 15, reading, 0 )) reading15,
 19  sum(decode(readhour, 16, reading, 0 )) reading16,
 20  sum(decode(readhour, 17, reading, 0 )) reading17,
 21  sum(decode(readhour, 18, reading, 0 )) reading18,
 22  sum(decode(readhour, 19, reading, 0 )) reading19,
 23  sum(decode(readhour, 20, reading, 0 )) reading20,
 24  sum(decode(readhour, 21, reading, 0 )) reading21,
 25  sum(decode(readhour, 22, reading, 0 )) reading22,
 26  sum(decode(readhour, 23, reading, 0 )) reading23,
 27  sum(decode(readhour, 24, reading, 0 )) reading24

 28 from t
 29 group by name, readdate
 30 /

View created.

SQL>
SQL> select name, readdate, reading1, reading2, reading3   2 from t_view
  3 /

NAME                           READDATE    READING1   READING2   READING3
------------------------------ --------- ---------- ---------- ----------
system                         19-FEB-99         25         50         75

>this is how other table: tab1row looks like
>
>NAME READDATE READING1 READING2 READING3 READING4
>
>
>this it the sql:
>
>INSERT INTO TAB1ROW (NAME,READDATE,READING1,READING2,READING3,READING4)
>(select t1.name, t1.readdate, t1.reading HOUR1 , t2.reading HOUR2,
>t3.reading HOUR3 , t4.reading HOUR4
>from tab24row t1, tab24row t2, tab24row t3, tab24row t4
>where t1.name = 'system'
>and t1.readhour = 1
>and t2.name = t1.name
>and t2.readhour =2
>and t3.name = t1.name
>and t3.readhour = 3
>and t4.name = t1.name
>and t4.readhour = 4);
>
>and this is result
>
>
>SQL> SELECT * FROM TAB1ROW;
>
>NAME READDATE READING1 READING2 READING3 READING4
>
>system 19-feb-1999 1000 1500 2000 1400
>
>
>thanks for suggustions.
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 19 1999 - 14:20:19 CST