Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Pivots in Oracle 8i
Dave,
check out the DECODE function. You can achieve your desired result of 12
data columns - one per month - by including 12 DECODE's in your SELECT
clause. You only identify thew source table once - 12-way join is not
needed. Most Oracle SQL books discuss how the use the DECODE function to
emulate the behaviour of Microsoft's TRANSFORM/PIVOT clauses when you know
beforehand what the data columns will be.
Jon Blake
Dave Godbey wrote:
> I'm looking for stategies for multi-joining a single table in Oracle. I
> imagine there must be some. Here is what I need to do:
>
> I have a table that holds financial records stored on a monthly basis.
> For a given set of criteria including year, I need to
> return a set of records that descrbe the financial activity over that
> year, eg
>
> Criteria... Jan Feb Mar April May etc.
>
> The approach I'm following runs rather sluggish, eg:
> With a SQL statement I instantiate 12 copies of the table with each copy
> bringing back the particular month of interest,
> stringing all 12 together for the complete yearly view of each
> record.horizontally.
>
> Are there better ways to do this, or should I sharpen up my performance
> tuning skills?
>
> I'm running Oracle 8i.
>
> Thanks,
> Dave Godbey
Received on Wed Oct 25 2000 - 00:33:13 CDT