Re: Is this possible in Oracle SQL?

[Quoted] Hi, Andrew !

I believe you need a query like this:

select main.dat, first.anal_total, second.anal_total, main.total  from head main,
 (select anal_total, id from det where ANLAYSIS_CODE='A') first,  (select anal_total, id from det where ANLAYSIS_CODE='B') second where main.id=first.id and main.id=second.id;

I assume that

   HEAD is a header table
    DET is a detail table
    DAT is a date column

A D Kendall wrote:

> I wonder if anyone can help with this SQL syntax problem...
>
> I have 2 tables linked by "ID" as follows:
>
> Header Table Detail Table
>
> ID DATE TOTAL ID ANLAYSIS_CODE ANAL_TOTAL
> ---------------------------- --------------------------------
> -----------------------
> 1 10/4/00 12009 1 A
> 120
> 2 11/4/00 19098 1 B
> 340
> 3 12/4/00 23889 2 A
> 98
> 2 B
> 67
> 3 A
> 13
> 3 B
> 28
>
> I need a query which will produce an output like this from the two tables:
>
> DATE A B TOTAL
> --------------------------------------------------------------
> 10/4/00 120 340 12009
> 11/4/00 98 67 19098
> 12/4/00 13 28 23889
>
> i.e. the data in the ANALYSIS_CODE field in the detail table becomes the
> heading of a column in the results and the associated ANAL_TOTAL becomes the
> data of this column.
>
> Is this possible in Oracle 8i ? I am lead to believe it is possible in MS
> Access using a TRANSPOSE keyword (or similar) in the SELECT statement.
>
> Any help or suggestions would be greatly appreciated.
>
> Thanks in advance,
> --
> Andrew Kendall
Received on Thu Jun 01 2000 - 00:00:00 CEST