Will analytics help?

From: Miggins <mtproc_at_yahoo.co.uk>
Date: 16 Mar 2006 04:05:54 -0800
Message-ID: <1142510754.512878.88750@v46g2000cwv.googlegroups.com>

Hi,

I have the following table

ent_cancellations
cnc_id             number(8)
cnc_period       date
cnc_code         varchar2(10)
cnc_rate           varchar2(5)
cnc_duration     number(1)
cnc_cancelled   varchar2(1)

Possible values for these fields are

CNC_RATE can = 'SM', 'FM', 'NM'
CNC_DURATION can = 1, 2 or 3
CNC_CANCELLED can = Y or N

and am wanting to extract the data as follows.

So grouped on cnc_code within cnc_period the count of records in the following groups

CNC_RATE within CNC_DURATION within CNC_CANCELLED. So the columns for each group would be as follows.......

CNC_CANCELLED Y
CNC_DURATION 1
CNC_RATE SM

CNC_CANCELLED Y

CNC_DURATION 1
CNC_RATE FM

CNC_CANCELLED Y
CNC_DURATION 1
CNC_RATE NM

CNC_CANCELLED Y

CNC_DURATION 2
CNC_RATE SM

CNC_CANCELLED Y
CNC_DURATION 2
CNC_RATE FM

CNC_CANCELLED Y

CNC_DURATION 2
CNC_RATE NM
....
....
.... Through to
....

CNC_CANCELLED N
CNC_DURATION 3
CNC_RATE SM

CNC_CANCELLED N

CNC_DURATION 3
CNC_RATE FM

CNC_CANCELLED N
CNC_DURATION 3
CNC_RATE NM

I can see a way of doing this using decode but it looks awful messy and convaluted. Is there a way using analytics that may simplify the query.

Am running on 9.2.0.4

Any help greatly appreciated. Received on Thu Mar 16 2006 - 06:05:54 CST