Re: Pivot Table from Access to Oracle

MGFoster <me_at_privacy.com> wrote:

> Use the CASE statement to create the summations:

Thank you very much for your answer.
But there is another problem I face:

I have the given table and want to convert rows to columns.

NR	ARTICLE	DAY         STOCK   VALUE
073639	C1	31.12.2003	158723	15,3326418
073639	C1	31.01.2004	158723	15,40247992
073639	C1	29.02.2004	158723	14,67552858
073639	C1	31.03.2004	158723	14,86123449
073639	C1	30.04.2004	158723	14,76917515
079373	G1	31.12.2003	158667	9,9008208
079373	G1	31.01.2004	158988	10,2388272
079373	G1	29.02.2004	158988	10,34057952
079373	G1	31.03.2004	158988	10,37873664
079373	G1	30.04.2004	160062	10,6601292
085581	M1	31.12.2003	136398	100,31254512
085581	M1	31.01.2004	136398	100,5798852
085581	M1	29.02.2004	136898	102,09031452
085581	M1	31.03.2004	136898	103,26626834
085581	M1	30.04.2004	136899	102,263553
085820	G2	31.12.2003	160910	11,2234725
085820	G2	31.01.2004	160910	11,2604818
085820	G2	29.02.2004	160910	11,3312822
085820	G2	31.03.2004	143300	10,139908
085820	G2	30.04.2004	143300	10,124145

That's what it should be:

DAY		C1	G2	G1	M1
31.12.2003	158723	160910	158667	136398
31.01.2004	158723	160910	158988	136398
29.02.2004	158723	160910	158988	136898
31.03.2004	158723	143300	158988	136898
30.04.2004	158723	143300	160062	136899

That's what I get:
DAY		C1	G1	M1	G2
31.12.2003	158723	158667	136398	143300
31.01.2004	158723	158667	136398	143300
29.02.2004	158723	158667	136398	143300
31.03.2004	158723	158667	136398	143300
30.04.2004	158723	158667	136398	143300

With Access' First() function, this works, but not with the following query:

SELECT T.DAY,
           (select distinct FIRST_VALUE(STOCK) over (order by STOCK from T where NR='073639') "C1",

           (select distinct FIRST_VALUE(STOCK) over (order by STOCK from T where NR='079373') "G1",

           (select distinct FIRST_VALUE(STOCK) over (order by STOCK from T where NR='085581') "M1",

           (select distinct FIRST_VALUE(STOCK) over (order by STOCK from T where NR='085820') "G2"
FROM T
GROUP BY DAY; How do I have to use this to get only the right values?

Additionaly, what exactly is ROWS/RANGE UNBOUNDED PRECEDING good for and could it be helpful?

Thanks in advance and have a nice day,
mic Received on Fri May 21 2004 - 07:59:46 CDT