Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Please Help: How to make a cross table query in a Oracle Database

Re: Please Help: How to make a cross table query in a Oracle Database

From: <syakobson_at_erols.com>
Date: 1997/10/12
Message-ID: <876672186.27150@dejanews.com>#1/1

Oracle does not have a SQL statement to pivot a table (like TRANSFORM in MS Access). However in certain cases you can do that with a SELECT statement. In your case it is possible if you know all the distinct values in column NAME and the number of them does not exceed 254 (ORACLE allows no more that 255 columns per table and you need one for ID column). Assuming these conditions are met, all you do is:

SELECT ID,

                SUM(DECODE(NAME,'Ad',AMOUNT,NULL)) Ad,
                SUM(DECODE(NAME,'Ger',AMOUNT,NULL)) Ger,
                SUM(DECODE(NAME,'Eric',AMOUNT,NULL)) Eric,
                SUM(DECODE(NAME,'Randy',AMOUNT,NULL)) Randy
    FROM Your_Table
    GROUP BY ID; Solomon Yakobson.

In article <01bcd6fd$d5d7f060$LocalHost_at_ger>,   "Ger Otten" <gmjotten_at_turnkiek.nl> wrote:
>
> In MS Access it is very ease to create a cross table query that show data
> that is in the database like this:
>
> name amount Id
> Ad 155,00 1
> Ger 30,00 1
> Erik 33,45 1
> Randy 44,56 1
> Ad 56,78 2
> Erik 23,45 2
> Ger 45,67 2
> Randy 78,78 2
>
> wil be show in a cross table as follows:
>
> Id Ad Erik Ger Randy
> 1 155,00 33,45 30,00 44,56
> 2 56,78 23,45 45,67 78,78
>
> It shows the Name filed as collumns, where the field ID is a record and it
> show for name the field amount.
>
> The syntax in Access is:
>
> TRANSFORM First(([amount])) AS [The Value]
> SELECT Table1.Id
> FROM Table1
> GROUP BY Table1.Id
> PIVOT Table1.naam;
>
> How can I do such a query on a Oracle Database ?
>
> Or do I have to make a query for each name field in the table and join them
> in another tabel on the field Id.
>
> For example:
>
> Query 1: SELECT DISTINCTROW Table1.aantal AS Ger, Table1.Id FROM Table1
> WHERE ((Table1.naam="Ger"));
> Query 2: SELECT DISTINCTROW Table1.aantal AS Ad, Table1.Id FROM Table1
> WHERE ((Table1.naam="Ad"));
> Query 3: SELECT DISTINCTROW Table1.aantal AS Erik, Table1.Id FROM Table1
> WHERE ((Table1.naam="Erik"));
>
> next join them in:
>
> Query 4:
>
> SELECT DISTINCT Query1.Ger, Query2.Ad, Query3.Randy
> FROM (Query1 INNER JOIN Query2 ON Query1.Id = Query2.Id) INNER JOIN Query3
> ON Query2.Id = Query3.Id;
>
> But most the time the number of different names in the Table are not known
> !
> This is much more work then te cross table query in Access.
> Is there an easy solution in Oracle ?
>
> Thank you very much for your help.
>
> Ger Otten
>
> Email: gmjotten_at_worldonline.nl
>
>

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Sun Oct 12 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US