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

From: S. Johnson <scjonson_at_fyiowa.infi.net>
Date: 1997/01/21
Message-ID: <32E58FEF.6CB9@fyiowa.infi.net>#1/1

Charanjeet wrote:
>
> 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

There are ways to do crosstab reports using SQL Plus. The only dynamic part will be down the side as like the date in your example. Accross the top pretty much has to be hard coded using the decode statement as follows.

SELECT  registration_date "Registration Date", 
	nvl(avg(decode(item_type,'A',cost,null)),0) "Item type A",
	nvl(avg(decode(item_type,'B',cost,null)),0) "Item type B",
	nvl(avg(decode(item_type,'C',cost,null)),0) "Item type C"

  FROM xyz
GROUP BY registration_date;

This should produce results similar to:

Registration Date Item type A Item type B Item type C

-----------------  -----------  -----------  -----------
10/10/96	          9999            0          999
10/11/96		     0           99          999

The date is the only dynamic thing. Accross the top will have to be hardcoded or built dynamically.

Steve J. Received on Tue Jan 21 1997 - 00:00:00 CST