Is this possible in Oracle SQL?

From: A D Kendall <nobody_at_nowhere.com>
Date: 2000/06/01
Message-ID: <8h5er7$mqa$1@supernews.com>#1/1

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

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 CDT