Need help with Pivot Query for daily Production Data.

From: LT Cheah <LT_Cheah_at_usa.net>
Date: Tue, 20 Apr 2004 20:57:36 +0800
Message-ID: <a95a8019gn8omk3v0l7jqpf2vkmevslk9t@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. Received on Tue Apr 20 2004 - 07:57:36 CDT