Re: Hard Consult

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jun 2001 04:51:46 -0700
Message-ID: <9gkq0i02eh3@drn.newsguy.com>

In article <3B2D8E22.D27F04F5_at_edb.ericsson.se>, Fabio says...
>
>I have a table with the format below:
>
>ID Sev Date
>--------- -------- --------
>ID001 1 05-10-2001
>ID002 2 x
>ID003 4 x
>
>
>
>The range of Sev field is [1..5]
>
>I need to make a consult that generate the output below:
>
>Date/Sev 1
>2 3 4 5
>05-01-2001 Count() Count() Count()
>Count()
>12-01-2001 ...
>... ...
>19-01-2001
>26-01-2001
>
>The date is grouped by week using the group by clause: GROUP BY (DATE -
>TO_CHAR(DATE,'D') + 1)
>
>Anyboby can help me
>
>Thanks.
>
>
>

decode is great for doing transpositions like this:

ops$tkyte_at_ORA8I.WORLD> create table t ( id varchar2(10), sev int, dt date );

Table created.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> begin
  2          for i in 1 .. 100
  3          loop
  4                  insert into t ( id, sev, dt )
  5                  values
  6                  ( 'ID' || to_char( dbms_random.value( 1000, 
                                                           1020 ), 'fm0000' ),
  7                    dbms_random.value( 1, 5 ),
  8                    sysdate + dbms_random.value( 1, 50 )
  9                  );
 10          end loop;

 11 end;
 12 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> select trunc( dt, 'w' ),
  2             sum( decode( sev, 1, 1, 0 ) ) sev_1,
  3             sum( decode( sev, 2, 1, 0 ) ) sev_2,
  4             sum( decode( sev, 3, 1, 0 ) ) sev_3,
  5             sum( decode( sev, 4, 1, 0 ) ) sev_4,
  6             sum( decode( sev, 5, 1, 0 ) ) sev_5

  7 from t
  8 group by trunc( dt, 'w' )
  9 /

TRUNC(DT, SEV_1 SEV_2 SEV_3 SEV_4 SEV_5

--------- ---------- ---------- ---------- ---------- ----------
15-JUN-01          0          1          2          1          3
22-JUN-01          3          4          3          5          3
29-JUN-01          3          1          0          0          2
01-JUL-01          1          3          0          1          1
08-JUL-01          1          4          2          3          2
15-JUL-01          1          2          6          4          3
22-JUL-01          0          5          5          6          0
29-JUL-01          0          1          4          0          0
01-AUG-01          1          0          4          7          2

9 rows selected.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jun 18 2001 - 06:51:46 CDT