Re: Oracle PIVOT with Mulitpl Years (mulitple tables)

Peter wrote:

> I am working on a project which is asking for a specific format of
> reporting. Until now I was able to get this done in ACCESS, but since
> the data is now in Oracle I can do much better things. I have been
> checking all the groups on how to create a PIVOT and I am getting the
> general idea here, however maybe somebody can give some good
> guidelines on how to achieve the following.
>
> 1. The data is stored in one big datawarehouse and all linking tables
> will not be included (these tables will be linked later in Crystal,
> like name, adres, product description etc.
>
> 2. The normal display for this request is that I need to compare
> several years (in detail by month) to see progress in numbers and
> Percentages (crystal)
>
> Like:
> Customer (this actually a department)
> PRDGRP (this is group) Jan Year 1 Jan Year 2 Feb Year 1 Feb Year 2
> H01 100 120 90 101
>
>
> I have 2 ideas on achieving this, one is a view with the decode
> command used or I will create a procedure that will fill another table
> with these records. The number of records in the table are 2 million
> (and not all very clean so it need to have some filter tweaking to get
> the data right)
>
> I need to the most simple version of this because this table also need
> to work on money and quantity (the report will show either Sales in €
> or in quantities)

I'd create a materialized view using DECODE to pivot the data.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)