Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access.
Given are two tables to join:
T_FIRM:
FIRM_ABBR VARCHAR2(3 BYTE),
FIRM_LONG VARCHAR2(70 BYTE),
CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
"FI1", "Company Number One"
"FI2", "Company Number Two"
....
"FOO", "another Company"
T_DATA:
BEZEICHNUNG VARCHAR2(255 BYTE),
DATUM DATE,
VOLUMEN_EUR FLOAT(126),
AK VARCHAR2(10 BYTE),
AW VARCHAR2(10 BYTE),
AR VARCHAR2(10 BYTE),
MF VARCHAR2(10 BYTE),
AP VARCHAR2(10 BYTE),
PK VARCHAR2(10 BYTE),
PI VARCHAR2(10 BYTE),
EU VARCHAR2(10 BYTE),
ZG VARCHAR2(10 BYTE),
FT VARCHAR2(10 BYTE),
ET VARCHAR2(10 BYTE),
ST NUMBER,
DE VARCHAR2(10 BYTE),
EM VARCHAR2(10 BYTE),
DU VARCHAR2(10 BYTE),
RK VARCHAR2(10 BYTE),
BR VARCHAR2(10 BYTE),
KA VARCHAR2(10 BYTE),
AM VARCHAR2(10 BYTE),
PM VARCHAR2(10 BYTE),
PT VARCHAR2(10 BYTE),
TT VARCHAR2(10 BYTE),
FIRM VARCHAR2(10 BYTE),
Columns AK to TT are Foreign Key references to a lot of separate
tables.
AK can be one of 16 values, i.e. 'A1','G4','I1', 'C2'. These 16 rows
should become columns. ZG can be one of 'G','P','S'.
I would like to store this Pivot table as a View:
TRANSFORM Sum([VOLUMEN_EUR]/1000000) AS Vol
SELECT T_FIRM.FIRM_LONG, T_DATA.Datum, Sum([VOLUMEN_EUR]/100000000) AS
Summe1
FROM T_DATA INNER JOIN T_FIRM ON T_DATA.FIRM = T_FIRM.FIRM_ABBR
WHERE (((T_DATA.Datum)=#2/27/2004#))
GROUP BY T_FIRM.FIRM_LONG, T_DATA.Datum
PIVOT T_DATA.ZG;
I've read about such a construct, but this only creates
columns from rows, but without summing up anything:
select DA.FIRM, DA.DATUM, DA.AK,
decode (DA.AK, 'A1', DA.AK) A1,
decode (DA.AK, 'A2', DA.AK) A2,
decode (DA.AK, 'G1', DA.AK) G1,
decode (DA.AK, 'G2', DA.AK) G2,
decode (DA.AK, 'G3', DA.AK) G3,
decode (DA.AK, 'G4', DA.AK) G4,
decode (DA.AK, 'G5', DA.AK) G5,
decode (DA.AK, 'G6', DA.AK) G6,
decode (DA.AK, 'G7', DA.AK) G7,
decode (DA.AK, 'G8', DA.AK) G8,
decode (DA.AK, 'I1', DA.AK) I1,
decode (DA.AK, 'R1', DA.AK) R1,
decode (DA.AK, 'R2', DA.AK) R2,
decode (DA.AK, 'X', DA.AK) X
FROM T_DATA DA
group by DA.FIRM, DA.DATUM, DA.AK
Any help would be greatly appreciated.
mic
4  2742 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Use the CASE statement to create the summations:
select DA.FIRM, DA.DATUM, DA.AK,
SUM(CASE DA.AK WHEN 'A1' THEN DA.AK ELSE NULL END) A1,
SUM(CASE DA.AK WHEN 'A2' THEN DA.AK ELSE NULL END) A2,
SUM(CASE DA.AK WHEN 'G1' THEN DA.AK ELSE NULL END) G1,
SUM(CASE DA.AK WHEN 'G2' THEN DA.AK ELSE NULL END) G2,
SUM(CASE DA.AK WHEN 'G3' THEN DA.AK ELSE NULL END) G3,
SUM(CASE DA.AK WHEN 'G4' THEN DA.AK ELSE NULL END) G4,
SUM(CASE DA.AK WHEN 'G5' THEN DA.AK ELSE NULL END) G5,
SUM(CASE DA.AK WHEN 'G6' THEN DA.AK ELSE NULL END) G6,
SUM(CASE DA.AK WHEN 'G7' THEN DA.AK ELSE NULL END) G7,
SUM(CASE DA.AK WHEN 'G8' THEN DA.AK ELSE NULL END) G8,
SUM(CASE DA.AK WHEN 'I1' THEN DA.AK ELSE NULL END) I1,
SUM(CASE DA.AK WHEN 'R1' THEN DA.AK ELSE NULL END) R1,
SUM(CASE DA.AK WHEN 'R1' THEN DA.AK ELSE NULL END) R2,
SUM(CASE DA.AK WHEN 'X' THEN DA.AK ELSE NULL END) X
FROM T_DATA DA
group by DA.FIRM, DA.DATUM, DA.AK
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQIhNMYechKqOuFEgEQJsAACfe7UmJHjaAr8/Lqrddfd7zjLVs+oAoL0V
LMWK2hbIHZBU4rZ+Q7q1No1i
=Tzm4
-----END PGP SIGNATURE-----
Michael John wrote: Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access.
Given are two tables to join:
T_FIRM:
FIRM_ABBR VARCHAR2(3 BYTE),
FIRM_LONG VARCHAR2(70 BYTE),
CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
"FI1", "Company Number One"
"FI2", "Company Number Two"
...
"FOO", "another Company"
T_DATA:
BEZEICHNUNG VARCHAR2(255 BYTE),
DATUM DATE,
VOLUMEN_EUR FLOAT(126),
AK VARCHAR2(10 BYTE),
AW VARCHAR2(10 BYTE),
AR VARCHAR2(10 BYTE),
MF VARCHAR2(10 BYTE),
AP VARCHAR2(10 BYTE),
PK VARCHAR2(10 BYTE),
PI VARCHAR2(10 BYTE),
EU VARCHAR2(10 BYTE),
ZG VARCHAR2(10 BYTE),
FT VARCHAR2(10 BYTE),
ET VARCHAR2(10 BYTE),
ST NUMBER,
DE VARCHAR2(10 BYTE),
EM VARCHAR2(10 BYTE),
DU VARCHAR2(10 BYTE),
RK VARCHAR2(10 BYTE),
BR VARCHAR2(10 BYTE),
KA VARCHAR2(10 BYTE),
AM VARCHAR2(10 BYTE),
PM VARCHAR2(10 BYTE),
PT VARCHAR2(10 BYTE),
TT VARCHAR2(10 BYTE),
FIRM VARCHAR2(10 BYTE),
Columns AK to TT are Foreign Key references to a lot of separate
tables.
AK can be one of 16 values, i.e. 'A1','G4','I1', 'C2'. These 16 rows
should become columns. ZG can be one of 'G','P','S'.
I would like to store this Pivot table as a View:
TRANSFORM Sum([VOLUMEN_EUR]/1000000) AS Vol
SELECT T_FIRM.FIRM_LONG, T_DATA.Datum, Sum([VOLUMEN_EUR]/100000000) AS
Summe1
FROM T_DATA INNER JOIN T_FIRM ON T_DATA.FIRM = T_FIRM.FIRM_ABBR
WHERE (((T_DATA.Datum)=#2/27/2004#))
GROUP BY T_FIRM.FIRM_LONG, T_DATA.Datum
PIVOT T_DATA.ZG;
I've read about such a construct, but this only creates
columns from rows, but without summing up anything:
select DA.FIRM, DA.DATUM, DA.AK,
decode (DA.AK, 'A1', DA.AK) A1,
decode (DA.AK, 'A2', DA.AK) A2,
decode (DA.AK, 'G1', DA.AK) G1,
decode (DA.AK, 'G2', DA.AK) G2,
decode (DA.AK, 'G3', DA.AK) G3,
decode (DA.AK, 'G4', DA.AK) G4,
decode (DA.AK, 'G5', DA.AK) G5,
decode (DA.AK, 'G6', DA.AK) G6,
decode (DA.AK, 'G7', DA.AK) G7,
decode (DA.AK, 'G8', DA.AK) G8,
decode (DA.AK, 'I1', DA.AK) I1,
decode (DA.AK, 'R1', DA.AK) R1,
decode (DA.AK, 'R2', DA.AK) R2,
decode (DA.AK, 'X', DA.AK) X
FROM T_DATA DA
group by DA.FIRM, DA.DATUM, DA.AK
MGFoster <me@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
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" <am********@gmx.at> wrote in message
news:8d**************************@posting.google.c om... MGFoster <me@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
"Kevin Crosbie" <ca**************@yahoo.com> wrote in message news:<7e******************************@news.terane ws.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" <am********@gmx.at> wrote in message
news:8d**************************@posting.google.c om... MGFoster <me@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 This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Michael John |
last post: by
|
2 posts
views
Thread by Rob |
last post: by
|
1 post
views
Thread by SJM |
last post: by
|
8 posts
views
Thread by Jerome Ranch |
last post: by
|
9 posts
views
Thread by PeteCresswell |
last post: by
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
