Re: Question about SQL syntax

Take a look at the decode statement. We often use this to turn rows into columns for reporting.

Michael

In article <8ut5pb$die$1_at_nnrp1.deja.com>,   gcouch_at_my-deja.com wrote:
> 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-
> to-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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 15 2000 - 08:45:15 CST