Re: Is this possible in Oracle SQL?

[Quoted] Thanks a lot, thats certainly got me looking in the right direction, but I won't know how many "ANALYSIS_CODES" there are. In my example I used two (A and B) but there could be any number.
[Quoted] For example, if there are 5 records in the detail table where ANALYSIS_CODE [Quoted] = A,B,C,D,E and F, then I need five columns in the results. (Ideally I would [Quoted] like the columns to be named A,B,C,D,E and F (i.e. whatever data was in the [Quoted] ANALYSIS_CODE field) but that's probably another problem!

Tereschenko I.V. <igor_v_t_at_mail.ru> wrote in message news:39365D1D.80616006_at_mail.ru...
> 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
  >