Re: Can I do cross-tab query in Oracle

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 2000/03/14
Message-ID: <8al9tb$7jd$1@ctb-nnrp1.saix.net>#1/1

circetsui wrote in message <#iGNNVri$GA.242_at_cpmsnbbsa03>...
>Can anybody tell me if there is speical form of SQL in Oracle for cross-tab
>query?

You do not need a "special form" of SQL - plain vanilla flavoured ANSI 92 SQL can do the job fine.

The trick is to create a pivot table, and join the data set with that. The result set will then be pivoted or cross-tabbed, i.e. you make columns into rows and rows into columns.

This is best explained with a little example.

YEARLY_RESULTS:
Year Quarter Value
---- ------- -----

94   1       100
94   2       150
94   3       170
94   4       200
95   1       190
95   2       210
95   3       205
95   4       215

OK, you want the data of the above table to be displayed (via a SQL SELECT statement) as follows:

Year Q1 Q2 Q3 Q4
---- --- --- --- ---
94 100 150 170 200
95 190 210 205 215

How to do it? First you need to create (what I call) a pivot table. We want to pivot the QUARTER column and for a quarter have 4 distinct values. So we create the following table:

PIVOT_TABLE:
Quarter Q1 Q2 Q3 Q4
------- -- -- -- --

1       1   0   0   0
2       0   1   0   0
3       0   0   1   0
4       0   0   0   1

For Quarter One, only Q1 is "valid" and the others are "invalid". Thus we mark the Q1 column for Quarter 1 with a one and the other columns with zeros. Ditto for the other columns (reminiscent of bitmasking).

OK, now we join our data set with the pivot table. SELECT
  yearly_results.year,

  SUM( yearly_results.value * pivot_table.q1) "Q1",
  SUM( yearly_results.value * pivot_table.q2) "Q2",
  SUM( yearly_results.value * pivot_table.q3) "Q3",
  SUM( yearly_results.value * pivot_table.q4) "Q4"
FROM yearly_results,
     pivot_table

WHERE yearly_results.quarter = pivot_table.quarter GROUP BY
  yearly_results.year

The concept here is that we create 4 brand new columns - one for every quarter. Then we sum the values from the yearly results table for each of these 4 columns. However, we do not want to add quarter 1's value into the wrong columns. Enter the pivot table - it causes quarter 1's value to be multiplied with zero for any other quarter except quarter 1.

I hope that this example is clear enough (and correct - it has been a while since I last used it myself! ;-). I found it a bit difficult to grasp it myself at first, but as soon as the light goes on upstairs, it is actually a very simple and elegant solution.

regards,
Billy Received on Tue Mar 14 2000 - 00:00:00 CST