Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can I do cross-tab query in Oracle
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
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 - 06:04:55 CST