using SQL to create a "cross-table" report

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 Received on Mon Jan 06 1997 - 00:00:00 CST