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  2865 
-----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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Michael John |
last post by:
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 ...
|
by: Rob |
last post by:
I'm just getting around to using pivot tables and charts. I find the
Pivot table interface to be INCREDIBLY frustrating.
When I view a table in Design view, then choose Pivot table view, I
get...
|
by: SJM |
last post by:
I have a reporting database that has a series of pre-defined Pivot Table
screens. What I would like to do is to enable users to be able to save their
own configurations. I found 2 ways of doing...
|
by: Jerome Ranch |
last post by:
Okay
So I've got pivot tables setup in Access 2003.
Only about 30K records in the current 2005 databases...the pivots
summarize the info in a number of nice ways.
I need to get the pivot tables...
|
by: PeteCresswell |
last post by:
I've got something called "Reference Rates".
The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury...
|
by: Pourya99 |
last post by:
Hello,
I have an Access Data Access Page which has a pivot table. The data source of the pivot table is a SQL database table. The data in the pivot table itself is not a problem.
I have a text...
|
by: Adu |
last post by:
Pivot Tables
--------------------------------------------------------------------------------
Hi guys,
I have two questions:
First Question: I have created a Pivot Table on my Access database....
|
by: benchpolo |
last post by:
I have data extracted from Access db to Excel with a pivot table. Somehow, I am having issues with the pivot table were it doesnt update the totals. For example, the first extract i did in Access...
|
by: STUFIX |
last post by:
Hi all,
I created a pivot table that allowed users to select certain customers from a drop down list and display the relevant data - it worked fine but has now stopped allowing them to do that.
...
|
by: mld01s |
last post by:
I really need help!!! I dont know if its possible to share pivot tables, or see pivot tables in other machines that the one where the tables were created.
This is what happens:
I created a...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
