Re: Is this possible in Oracle SQL?

Hi Kendall (why all those newsgroups?)
Try to look at some tools - the SQL language does not per default support a dynamic number of columns, but lots of tools do (all it requires is dynamic SQL and a pre-statement to analyse how many columns are needed). Oracle Discoverer is a great tool for this, but others do that too. I'm fairly sure, that the MS Access Frontend can do matrixes too. Oracle Reports is able to create these kinds of reports too. Crystal reports even so.

Dynamic number of columns makes a big problem coding for the results. If you're going to use the statement in PL/SQL consider turning the A,B,C ... columns into rows instead. You're looking for presentation - SQL is mostly just data-retrieval.

Regards
  P. Larsen

"A D Kendall" <nobody_at_nowhere.com> wrote in message news:8h5er7$mqa$1_at_supernews.com...
> 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 Fri Jun 02 2000 - 00:00:00 CDT