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

PLATINUM InfoReports gives you all the functionality you need to create dynamic crosstabs reports against Oracle (and other DBMSs). For more information see our website at http://www.platinum.com or contact PLATINUM technology in the UK at +44.1908.248400.

Have Fun !!!

Jaap Vink

-- 
                                         
The opinions above are mine. But if you decide to use them they suddenly
become yours.
They also become your responsibility.



Charanjeet <charan.j_at_midas-it.co.uk> wrote in article
<32E560DC.27A2_at_midas-it.co.uk>...


> 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

> 

--