Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Creating A Crosstab Query
Steve McDaniels wrote in message <666s3b$as8_at_yobi.sierra.com>...
>For those of you who haven't used MS-Access, a cross-tab query takes
>arbitrary data from fields in ROWS and "pivots" them into column headings.
Pivots can be done using standard SQL statements. It may be a wee bit complex, but it works. For example, let's say you have the following table: QRESULTS=( Year, Quarter, Turnover )
YEAR QUARTER TURNOVER
---- ------- --------
1996 1 100 1996 2 150 1996 3 200 1996 4 150 1997 1 200 1997 2 230 1997 3 220 1997 4 290
And you want to display the output as follows: YEAR Q1 TURNOVER Q2 TURNOVER Q3 TURNOVER Q4 TURNOVER ----- ----------- ----------- ----------- -----------
1996 100 150 200 150 1997 200 230 220 290
First thing to do is to create a pivot table to pivot the result. The pivot table construct is as follows. A "pivot column" to contain the values of the column to pivot on, and one "value column" per pivot value. Ok, we want to pivot by quarter and obtain 4 values per pivot.
PivotTable=(Quarter, Q1, Q2, Q3, Q4)
Now we create a bitmask in this table that defines the pivot "rules". A value of 1 indicates that the "value column" must be pivoted for the specified "pivot column". A zero means that the "value column" is not applicable to that "pivot column".
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
And now do the pivot using standard SQL:
SELECT
q.year,
SUM(q.turnover*p.q1) "Quarter 1", SUM(q.turnover*p.q2) "Quarter 2", SUM(q.turnover*p.q3) "Quarter 3", SUM(q.turnover*p.q4) "Quarter 4"
As you can see, in the "QUARTER 1" column in the above select statement, quarter 1 from the results table will be multiplied with 1 for the resulting sum of 100. The sums for quarter 2,3 and 4 values will all be zero as they are multiplied with zero (e.g. the pivot rule for quarter 2 says that Q1 is zero - see row 2 in the pivot table).
And that's how a crosstab/pivot query can be done! :-) I'm not sure who was the person who first though of this fairly simple and very elegant technique, but hats off to him.
regards,
Billy
Received on Fri Dec 05 1997 - 00:00:00 CST