Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem / Question about SQL-Statement

Re: Problem / Question about SQL-Statement

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 23 Nov 2006 07:43:48 -0800
Message-ID: <1164296628.841849.136560@k70g2000cwa.googlegroups.com> 





Thomas Kindermann wrote:


> Hello again,

>

> I have a short question, because I'm missing the keywords for this

> problem for searching in the documentation:

>

> existing Table output

>

> VDATE                     COMP SEGMENT_TYPE       MBYTES


> ------------------------- ---- ------------------ ----------------------

> 23.11.06                  806  INDEX              295.25


> 23.11.06                  806  TABLE              165


> 23.11.06                  809  INDEX              334.25


> 23.11.06                  809  TABLE              219.75


> 23.11.06                  811  INDEX              10021.8125


> 23.11.06                  811  TABLE              7972.3125


> 23.11.06                  812  INDEX              534.875


> 23.11.06                  812  TABLE              359.375


> 23.11.06                  813  INDEX              413.125


> 24.11.06                  813  TABLE              271.625


> 24.11.06                  806  INDEX              295.25


> 24.11.06                  806  TABLE              165


> 24.11.06                  809  INDEX              334.25


> 24.11.06                  809  TABLE              219.75


>

>

> SQL-Result:

>

> VDATE:             806        809         811        812    813


>                INDEX TABLE INDEX TABLE   INDEX TABLE


> 23.11.2004     ...    ...

> 24.11.2004     ...    ...

>

>

> Who can help me ?

>

> Thanks

>

> Thomas




Similar problems have been discussed here recently, although the
particular problem that you show here is a bit more complicated, as the
index and table data are on two different rows.  An Excel or Crystal
Reports pivot table (or what ever the terminology is for the package
that you have) would make easy work of this task.



It looks like you have a typo in date column of the  813 TABLE entry -
not a big problem.



The set up (VDATE should be a date column not varchar2):
CREATE TABLE T1 (



  VDATE VARCHAR2(8),



  COMP NUMBER(12),



  SEGMENT_TYPE VARCHAR2(10),



  MBYTES NUMBER(22,4));



INSERT INTO T1 VALUES ('23.11.06',806,'INDEX',295.25);
INSERT INTO T1 VALUES ('23.11.06',806,'TABLE',165);
INSERT INTO T1 VALUES ('23.11.06',809,'INDEX',334.25);
INSERT INTO T1 VALUES ('23.11.06',809,'TABLE',219.75);
INSERT INTO T1 VALUES ('23.11.06',811,'INDEX',10021.8125);
INSERT INTO T1 VALUES ('23.11.06',811,'TABLE',7972.3125);
INSERT INTO T1 VALUES ('23.11.06',812,'INDEX',534.875);
INSERT INTO T1 VALUES ('23.11.06',812,'TABLE',359.375);
INSERT INTO T1 VALUES ('23.11.06',813,'INDEX',413.125);
INSERT INTO T1 VALUES ('23.11.06',813,'TABLE',271.625);
INSERT INTO T1 VALUES ('24.11.06',806,'INDEX',295.25);
INSERT INTO T1 VALUES ('24.11.06',806,'TABLE',165);
INSERT INTO T1 VALUES ('24.11.06',809,'INDEX',334.25);
INSERT INTO T1 VALUES ('24.11.06',809,'TABLE',219.75);





COMMIT;


SELECT



  *


FROM



  T1


ORDER BY



  VDATE,



  COMP,



  SEGMENT_TYPE;


VDATE   COMP   SEGMENT_TYPE   MBYTES


23.11.06   806   INDEX   295.25
23.11.06   806   TABLE  165
23.11.06   809   INDEX   334.25
23.11.06   809   TABLE  219.75
23.11.06   811   INDEX   10021.8125
23.11.06   811   TABLE  7972.3125
23.11.06   812   INDEX   534.875
23.11.06   812   TABLE  359.375
23.11.06   813   INDEX   413.125
23.11.06   813   TABLE  271.625
24.11.06   806   INDEX   295.25
24.11.06   806   TABLE  165
24.11.06   809   INDEX   334.25
24.11.06   809   TABLE  219.75





Now to start transforming the data:


SELECT



  VDATE,



  COMP,


  FIRST_VALUE(MBYTES) OVER (PARTITION BY VDATE,COMP ORDER BY
SEGMENT_TYPE) I_MBYTES,
  FIRST_VALUE(MBYTES) OVER (PARTITION BY VDATE,COMP ORDER BY
SEGMENT_TYPE DESC) T_MBYTES




FROM



  T1


ORDER BY



  VDATE,



  COMP;


VDATE   COMP   I_MBYTES   T_MBYTES


23.11.06   806     295.25          165
23.11.06   806     295.25          165
23.11.06   809     334.25          219.75
23.11.06   809     334.25          219.75
23.11.06   811     10021.8125   7972.3125
23.11.06   811     10021.8125   7972.3125
23.11.06   812     534.875        359.375
23.11.06   812     534.875        359.375
23.11.06   813     413.125        271.625
23.11.06   813     413.125        271.625
24.11.06   806     295.25          165
24.11.06   806     295.25          165
24.11.06   809     334.25          219.75
24.11.06   809     334.25          219.75





By using analytical functions, I was able to combine the table and
index data for each VDATE/COMP onto the same row, but now there are
duplicate entries - adding DISTINCT will take care of those.



Now, there is still a problem, the data is still displayed in rows, and
not columns.  We need to find a way to slot the entries for each COMP
value into a different column.  The DENSE_RANK analytical function
helps with this task:



SELECT DISTINCT



  VDATE,



  COMP,


  FIRST_VALUE(MBYTES) OVER (PARTITION BY VDATE,COMP ORDER BY
SEGMENT_TYPE) I_MBYTES,
  FIRST_VALUE(MBYTES) OVER (PARTITION BY VDATE,COMP ORDER BY
SEGMENT_TYPE DESC) T_MBYTES,
  DENSE_RANK() OVER (ORDER BY COMP) SLOT


FROM



  T1


ORDER BY



  VDATE,



  COMP;


VDATE   COMP   I_MBYTES    T_MBYTES    SLOT


23.11.06	806     295.25          165                1
23.11.06	809     334.25          219.75           2
23.11.06	811     10021.8125   7972.3125      3
23.11.06	812     534.875        359.375         4
23.11.06	813     413.125        271.625         5
24.11.06	806     295.25         165                1
24.11.06	809     334.25         219.75            2





Now that we have the column slots defined, how can we put those entries
into a single row for a particular VDATE?  One method uses DECODE, MAX,
and GROUP BY (continue the pattern for the number of expected
table/index values):


SELECT



  VDATE,


  MAX(DECODE(SLOT,1,I_MBYTES,0)) S1_I_MB,
  MAX(DECODE(SLOT,1,T_MBYTES,0)) S1_T_MB,
  MAX(DECODE(SLOT,1,COMP,NULL)) S1_NAME,
  MAX(DECODE(SLOT,2,I_MBYTES,0)) S2_I_MB,
  MAX(DECODE(SLOT,2,T_MBYTES,0)) S2_T_MB,
  MAX(DECODE(SLOT,2,COMP,NULL)) S2_NAME,
  MAX(DECODE(SLOT,3,I_MBYTES,0)) S3_I_MB,
  MAX(DECODE(SLOT,3,T_MBYTES,0)) S3_T_MB,
  MAX(DECODE(SLOT,3,COMP,NULL)) S3_NAME




FROM



  (SELECT DISTINCT



    VDATE,



    COMP,



    FIRST_VALUE(MBYTES) OVER (PARTITION BY VDATE,COMP ORDER BY

SEGMENT_TYPE) I_MBYTES,



    FIRST_VALUE(MBYTES) OVER (PARTITION BY VDATE,COMP ORDER BY

SEGMENT_TYPE DESC) T_MBYTES,



    DENSE_RANK() OVER (ORDER BY COMP) SLOT

  FROM



    T1)


GROUP BY



  VDATE



ORDER BY



  VDATE;


VDATE  S1_I_MB  S1_T_MB  S1_NAME  S2_I_MB  S2_T_MB  S2_NAME  S3_I_MB

S3_T_MB   S3_NAME



23.11.06   295.25   165   806   334.25   219.75   809   10021.8125
7972.3125   811


24.11.06   295.25   165   806   334.25   219.75   809   0   0   (NULL)


Search the Oracle groups for recent posts by Michel Cadot and Charles
Hooper for a more elegant method to performing the last step, without
knowing how many table/index combinations may exist.



Charles Hooper


PC Support Specialist


K&M Machine-Fabricating, Inc.
Received on Thu Nov 23 2006 - 09:43:48 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US