Re: SQL question - crosstab in oracle

From: Jay Wade <fish_dba_at_hotmail.com>
Date: Tue, 08 Jul 2003 07:01:02 -0700
Message-ID: <F001.005C3DB4.20030708065424@fatcity.com>

There are several ways to handle this.
If you do a search for pivot on AskTom you will get a good sampling of them.

From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: SQL question - crosstab in oracle Date: Tue, 08 Jul 2003 06:34:24 -0800

I have a table with the following structure.

 CHECK_DATE                                             DATE
 PHYRDS                                                 NUMBER(20)
 PHYWRTS                                                NUMBER(20)
 PHYBLKRD                                               NUMBER(20)
 PHYBLKWRT                                              NUMBER(20)
 FILE#                                                       NUMBER
 FILE_NAME                                              VARCHAR2(120)

This records the IO on the different datafiles every 3 minutes.

I want to run a report on this table but I want the output to display on file# as a heading.Similar to iostat on UNIX. So my output might be:

Check DATE               FIle1                          File2
     File3 etc...............
               PHYRDS   PHYWRTS               PHYRDS   PHYWRTS
11:14:34            12708        202                    18         14
11:18:26            13038        211                    18         14
11:33:44            14290        243                    18         14
11:40:45            15008        245                    18         14
11:45:37            15459        249                    18         14
11:48:38            15776        250                    18         14
11:51:37            15864        251                    18         14
11:54:37            16029        254                    18         14
11:57:39            16210        254                    18         14
12:00:40            16311        254                    18         14
12:03:40            16439        256                    18         14

I could do this as a bit of plsq but Is it possible to generate this report with one bit of sql. (views,inline views, decodes. etc) thanks stephen

This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited.
This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jay Wade
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message