Re: Pivot Table from Access to Oracle

From: Kevin Crosbie <caoimhinocrosbai_at_yahoo.com>
Date: Fri, 21 May 2004 15:52:10 GMT
Message-ID: <7e518b941b384618d8737fb338bcc2d0@news.teranews.com>

This should do it:

select DAY

max(case when NR = 'C1' then STOCKelse null end) as C1,
max(case when NR = 'G2' then STOCK else null end) as G2,
max(case when NR = 'G1' then STOCK else null end) as G1,
max(case when NR = 'M1' then STOCK else null end) as M1

from T
group by DAY

Looks like you only want one value. You could also use a sum function if you want to add up all of the stock values.

don't know what you mean by ROWS/RANGE UNBOUNDED PRECEDING.

Kevin

"Michael John" <amstelchen_at_gmx.at> wrote in message news:8dbd6a8d.0405210459.1de6f1a6_at_posting.google.com...
> 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 - 10:52:10 CDT