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

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   Received on Sun Oct 12 1997 - 00:00:00 CDT