Re: Cross Tabs Queries in Oracle

From: Jim Ong <jong_at_belmont.com>
Date: Tue, 30 Jun 1998 13:55:03 -0700
Message-ID: <359950A4.D5D8FC8F@belmont.com>

Robert,

You can carry out this crosstab using a sequence of Oracle SQL statements.

Assume that you have the following data file with columns:

    dim_1 (values A, B)
    dim_2 (values 1, 2, 3)
    value (values 1, 2, 3)

e.g.,
ID dim_1 dim_2 value
1 A 1 1
2 A 2 2
3 A 2 1
4 B 1 3
5 B 3 2
6 B 3 1

First, "unfold" the table so that there is one column for each distinct value of DIM_2 using an
Oracle SQL trick (MAX(DECODE(...) GROUP BY...

CREATE VIEW TMP_14126_8 AS
SELECT ID ID,

         DIM_1 DIM_1,
         MAX(DECODE(DIM_2, '1',VALUE, NULL)) P1,
         MAX(DECODE(DIM_2, '2',VALUE, NULL)) P2,
         MAX(DECODE(DIM_2, '3',VALUE, NULL)) P3
FROM     TMP_14126_5

WHERE ID IS NOT NULL OR DIM_1 IS NOT NULL GROUP BY ID, DIM_1
;

The result of this table looks like:
ID DIM_1 P1 P2 P3

1,A,1
2,A, ,2
3,A, ,1
4,B,3
5,B, , ,2
6,B, , ,1

Then, compute the aggregate function (COUNT, SUM, etc.) as follows: CREATE VIEW TMP_14126_11 AS
SELECT count(P1) "DIM2_is_1", count(P2) "DIM2_Is_2", count

         (P3) "DIM2_is_3", DIM_1
FROM     TMP_14126_8 T1

GROUP BY DIM_1
;

The result of this table looks like:
DIM2_is_1, DIM2_IS_2, DIM2_IS_3, DIM_1
1 2 0 A
1 0 2 B

The UNFOLD step is a little tricky to write, as it requires that you first determine all distinct values of the dimension variable to unfold, and then write a DECODE(MAX(...)) clause for each distinct value.

The TableTrans data transformation product, developed by Belmont Research and marketed by IBM, automates the generation of step-by-step data transformations within an Oracle database. You add transformation steps, one at a time, using a GUI, and TableTrans executes each transformation step within Oracle.

Additional information about TableTrans is on the web at: www.belmont.com.

Best regards,
Jim Ong

Robert wrote:

> Hi,
> Does anyone know if crosstab style result sets can be obtained using raw
> SQL. (something similar to MS Access cross tab queries).
> Thanks in advance
> Robbie

Jim Ong, Director of Product Marketing             +1 650 635 0302
Belmont Research                              fax: +1 650 635 0303
1250 Bayhill Drive, Suite 101                     jong_at_belmont.com
San Bruno, CA 94066                         http://www.belmont.com

Received on Tue Jun 30 1998 - 15:55:03 CDT