Question about SQL syntax

I apologize up front that this question may be a little long. I'm not sure the most accurate way to phrase it so that the relevant information is passed along. Thanks for your patience.

I am returning data from an Oracle database into MS Access for further manipulation and reporting. I have a query that I'm not sure the correct syntax for. (I know basic SQL, but am mostly unfamiliar with Oracle).

I have two tables (let's call them 'Tbl' and 'SubTbl'). There is a one- -many relationship between Tbl and SubTbl. There is a common field (I'll call it 'Key') shared by the two tables.

I know how to join the two tables, based on the Key, and retrieve the information I need from both tables. However, I would like to "columnize" the row data from SubTbl.

In other words, I want to join the two tables based on the Key field, get the needed info from Tbl and selectively pull rows from SubTbl and put the SubTbl row data into columns in my Access table.

For example: let's say field 1 in SubTbl can contain one of four choices ('A', 'B', 'C', or 'D'). Field 2 has a related value to field 1. Let's assume it is a $ amount. I am only interested in the contents of Field2 from SubTbl where Field1 is either 'A' or 'C'.

In my current query, I can accomplish retrieving the data from both tables, but I get two records for each Key, one for when Field1 in SubTbl is 'A' (along with the corresponding Field2 of course) and one for Field1 when it is 'C'.

I had rather get one row for each Key with say a 'A-Field2value' and a 'C-Field2value' in my result so it is a straight record-for-record dump from the query results into my Access table.

What SQL syntax is required to accomplish this task of moving row data into column data?

Thanks in advance for the help.

Greg

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 14 2000 - 23:09:01 CST