Re: Is this possible in Oracle SQL?

A D Kendall <nobody_at_nowhere.com> wrote in message news:8h5rv1$ro1$1_at_supernews.com...
> 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.
> For example, if there are 5 records in the detail table where
 ANALYSIS_CODE
> = A,B,C,D,E and F, then I need five columns in the results. (Ideally I
 would
> like the columns to be named A,B,C,D,E and F (i.e. whatever data was in
 the
> ANALYSIS_CODE field) but that's probably another problem!

If all else fails, you can always generate SQL dynamically. By this I mean you could run a query on the detail table to retrieve all the analysis codes. On the basis of those results, you could generate the SQL you need for your main query programatically. E.g. if your query looks 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;

Then you can generate as many of the lines "(select anal_total, id from det where ANLAYSIS_CODE='X') x,"
as you need, then run the resulting query.

John

--
John Rusk
Innovus Ltd
inSIGHT Query Tool for Oracle:
http://www.innovus.co.nz/products/insight.htm