Re: SQL for Generating Crosstabs

From: Mike Lewis <mike.lewis_at_lgeenergy.com>
Date: 1997/07/31
Message-ID: <01bc9de1$8ef8be40$bb1e77aa@lewism.lgeenergy.com>#1/1

Have not done so with Oracle but used the following code to do cross tabs in MS-SQL Server. Unfortunately, this code does not provide for varying numbers of columns unless you are building the SQL itself dynamically. If so, the RowValue and ColumnValue fields could be identified from the source data.

create view RawData
as

select	RowValue,
	ColumnValue,
	Kount = Count(*),
	Average = AVG (datavalue)
from	basetable

group by RowValue, ColumnValue

Now you have to pivot the data.

create view PivotData
as

select	RowValue,
	col1 = CASE ColumnValue WHEN 1 THEN MIN(Average) ELSE NULL END,
	col2 = CASE ColumnValue WHEN 2 THEN MIN(Average) ELSE NULL END,
	col3 = CASE ColumnValue WHEN 3 THEN MIN(Average) ELSE NULL END,
	.....
	coln = CASE ColumnValue
		WHEN 1 THEN NULL
		WHEN 2 THEN NULL
		WHEN 3 THEN NULL
		...
		ELSE MIN(Average) END
from	RawData

group by RowValue, ColumnValue

The MIN function just displayed the value as there is only one record for each grouping. Any other aggregate function would have worked just as well. Now you have to compress the pivoted data to get your final result.

select RowValue,

	'Col1 ' = Sum(col1),
	'Col2 ' = Sum(col2),
	'Col3 ' = Sum(col3),
	...
	'Coln ' = Sum(coln)
from	PivotData

group by RowValue

Hope this helps. There might be a pivot function somewhere in Oracle, but I am new to that environment and don't know what it is.

Mike Lewis
Mike.Lewis_at_LGEenergy.com

Ganesh Laxminarayan <laxg_at_stratos.net> wrote in article <01bc9d5c$bb5cbb60$8f9a51d1_at_LAXG.stratos.net>...
> Hi,
>
> I would like to know if anybody has generated crosstab queries in Oracle
> 7.0 using PL/SQL+? If so, would it possible for you to guide me in the
> general direction of creating some queries?
>
> Thanks.
>
> Ganesh Laxminarayan
>
>
  Received on Thu Jul 31 1997 - 00:00:00 CDT