Pivot Table from Access to Oracle

From: Michael John <amstelchen_at_gmx.at>
Date: 22 Apr 2004 07:53:08 -0700
Message-ID: <8dbd6a8d.0404220653.48ade3d@posting.google.com>

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 Received on Thu Apr 22 2004 - 09:53:08 CDT