SQL for Generating Crosstabs

SQL for Generating Crosstabs

Post by Ganesh Laxminaraya » Fri, 01 Aug 1997 04:00:00



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

 
 
 

SQL for Generating Crosstabs

Post by Mike Lewi » Fri, 01 Aug 1997 04:00:00


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



Quote:> 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


 
 
 

1. Help: generate a crosstab - like query in SQL Server and publish it on the web

I had a MsAccess database with some query used to produce reports.
I upsized this DB to an Ms SQL Server database and I need to publish some
reports on the web, but I don't know how to create a crosstab query in the
SQL Server environment.
I can only use HTML and IDC on my web, not Java, VB scripts or others.

Thanks o anyone can help me.

2. Installing SQL Server 2000

3. How to have Sql2000 generate a CrossTab type recordset

4. Looking for a DBA view

5. Generate MS-ACCESS table using SQL Script generated by SQL Server

6. DOS SQL Link 4.5

7. Generating SQL to Generate Tables from Existing Database

8. How sql loader handle the error throw back from DB?

9. Crosstab with Oracle SQL, PL/SQL

10. Generate SQL script in SQL 2000 for SQL 6.5

11. SQL Server 6.0 and Query Crosstab (Access)

12. Crosstab-like queries in SQL Server

13. Crosstab Queries in SQL Server 6.5?