Re: Need help with Pivot Query for daily Production Data.

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: 23 Apr 2004 00:56:33 -0700
Message-ID: <cf15dee4.0404222356.13bd7ecc@posting.google.com>

LT Cheah <LT_Cheah_at_usa.net> wrote in message news:<a95a8019gn8omk3v0l7jqpf2vkmevslk9t_at_4ax.com>...
> Greetings;
>
> The simple script below gives me the daily production quantity and yield
> from the 3 test machines from the production floor for the last 7 days.
>
> SELECT TRUNC(TEST_DATE) TEST_DATE, MACH_ID,
> COUNT(*) QTY,
> AVG(DECODE(FAIL_CODE,0,100,0)) YLD
> FROM PRODUCTION_TABLE
> WHERE TEST_DATE>SYSDATE-7
> GROUP BY TRUNC(TEST_DATE), MACH_ID;
>
>
> TEST_DATE MACH_ID QTY YLD
> 04/01/2004 TESTER_A 5000 98
> 04/01/2004 TESTER_B 4878 72
> 04/01/2004 TESTER_C 6543 83
> 04/02/2004 TESTER_A 5555 95
> 04/02/2004 TESTER_B 4567 78
> 04/02/2004 TESTER_C 8644 89
> ....
> ....
> ...
>
>
> I like my resulting data to be tabulated as below:-
>
> TEST_DATE QTY_A YLD_A QTY_B YLD_B QTY_C YLD_C
> 04/01/2004 5000 98 4878 72 6543 83
> 04/02/2004 5555 95 4567 78 8644 89
> .....
> .....
> ....
> with the _A, _B and _C representing the individual tester's ID.
>
> What I currently do is to run the above script and then use EXCEL's vlookup
> function to get the tabulation as above. I understand that the ANALYTIC
> FUNCTIONS in ORACLE should be able to give me the results as above, but I'm
> not that familiar with them and am not able to get it running properly.
>
> The below is my attempt but it is still not correct. Also I do not know how
> to put in the yields for the 3 machines. Can some kind soul advise if the
> above tabulation is possible with ORACLE's ANALTIC FUNCTIONS (without PL/SQL
> if possible) ?
>
>
> SELECT TEST_DATE,
> MAX(DECODE(SEQ,1,QTY,NULL)) QTY_A,
> MAX(DECODE(SEQ,2,QTY,NULL)) QTY_B,
> MAX(DECODE(SEQ,3,QTY,NULL)) QTY_C
> FROM (SELECT TRUNC(TEST_DATE) "TEST_DATE", MACH_ID,
> COUNT(*) QTY OVER (PARTITION BY TRUNC(TEST_DATE),MACH_ID,
> ROW_NUMBER() OVER (PARTITION BY TRUNC(TEST_DATE), MACH_ID
> ORDER BY MACH_ID ASC NULLS LAST) SEQ
> FROM PRODUCTION_TABLE
> WHERE DATE_TIME>SYSDATE-7
> ORDER BY TRUNC(DATE_TIME), MACH_ID)
> WHERE SEQ<=3
> GROUP BY TEST_DATE;
>
>
>
> Thanks in advance.

Hi,

You appear to be following the examples given (p.577) in Chapter 12 "Analytic Functions" of "Expert One-on-One Oracle" by Thomas Kyte (A-Press:2003). In those examples, the requirement was to pivot by the relative ordering of values whereas you want to pivot by the value of the column, MACH_ID. My suggestion is given below.

Create some test data:

CREATE TABLE production_table AS

   SELECT

         DECODE(
            MOD( rownum, 3 ),
            0, 'TESTER_C',
            1, 'TESTER_A',
            2, 'TESTER_B'
         ) AS mach_id,
         TRUNC( SYSDATE ) + TRUNC( rownum / 1000 )
            AS test_date,
         MOD( rownum, 7 )
            AS fail_code
      FROM
         all_objects

;

When I run your first, part of the output is as follows: TEST_DATE MACH_ID QTY YLD

--------- -------- ---------- ----------
21-APR-04 TESTER_A        333 14.4144144
21-APR-04 TESTER_B        333 14.1141141
21-APR-04 TESTER_C        333 14.1141141
22-APR-04 TESTER_A        334 14.0718563
22-APR-04 TESTER_B        333 14.4144144
22-APR-04 TESTER_C        333 14.4144144
23-APR-04 TESTER_A        333 14.4144144
23-APR-04 TESTER_B        334 14.3712575
23-APR-04 TESTER_C        333 14.1141141

(Remainder of output deleted)

I then created a view ('production_view') on the original query only to simplify the presentation of my suggestion which is:

SELECT

      test_date,
      MAX( DECODE( mach_id, 'TESTER_A', qty ) )
         AS qty_a,
      MAX( DECODE( mach_id, 'TESTER_A', yld ) )
         AS yld_a,
      MAX( DECODE( mach_id, 'TESTER_B', qty ) )
         AS qty_b,
      MAX( DECODE( mach_id, 'TESTER_B', yld ) )
         AS yld_b,
      MAX( DECODE( mach_id, 'TESTER_C', qty ) )
         AS qty_c,
      MAX( DECODE( mach_id, 'TESTER_C', yld ) )
         AS yld_c
   FROM
      production_view
   GROUP BY
      test_date

;

Part of the output is:

TEST_DATE QTY_A YLD_A QTY_B YLD_B QTY_C YLD_C

--------- ---------- ---------- ---------- ---------- ---------- ----------
21-APR-04        333 14.4144144        333 14.1141141        333 14.1141141
22-APR-04        334 14.0718563        333 14.4144144        333 14.4144144
23-APR-04        333 14.4144144        334 14.3712575        333 14.1141141
24-APR-04        333 14.4144144        333 14.1141141        334 14.3712575

(Remainder of output deleted)

Douglas Hawthorne Received on Fri Apr 23 2004 - 02:56:33 CDT