"Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> wrote in message news:<7e518b941b384618d8737fb338bcc2d0_at_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
The columns used by a crosstab query can either be of a fixed number
or calculated at runtime. The former can be expressed as an Oracle
view if you employ derived tables. The latter cannot be expressed
using a view - a rather complicated stored procedure is required.
We have software that automatically converts Access crosstab queries
to Oracle. If you send me an Access database with the two tables and
crosstab query, I'll convert it for you.
/
Ron in SF
Received on Thu May 27 2004 - 20:27:57 CDT
