Re: Pivot Table from Access to Oracle

From: MGFoster <me_at_privacy.com>
Date: Thu, 22 Apr 2004 22:54:29 GMT
Message-ID: <F2Yhc.7502$e4.4378@newsread2.news.pas.earthlink.net>

-----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

Received on Thu Apr 22 2004 - 17:54:29 CDT