Re: using SQL to create a "cross-table" report

heath wrote:
>
> gidday,
>
> i'm looking for an sql-type solution for creating a "cross-table"
> report. By this i mean, a report which is dynamically created and
> groups rows meeting a particular criteria (eg same date) down the page
> and values for that criteria across the page.
>
> (Excel spreadsheet can perform this action by the "pivot-table" command.)
>
> ... now if that is as clear as mud ...
>
> For example, if a table "XYZ" has columns of
>
> registration_date date,
> item_type varchar(10),
> cost money
>
> How can i easily output a report which looks kinda like this:
>
> registration date item type a item type b item type c
> .... etc
> ----------------- ----------- ----------- -----------
> date $average cost $average cost $average cost
> for a for date for b for date for c for date
>
> The creation of the columns "item type x" will need to be dynamic
> depending on the number of different types for a given registration date
> range.
>
> .... can this be done easily?
>
> thanks if you can help,
> ben
You mean a cross tab query as in MS Access. We had wandered about this but could not find any thing within ORACLE. The only way we managed to do this was using PL/SQL. However this requires the use of cursors.

Have Fun Received on Tue Jan 21 1997 - 00:00:00 CET