Complex crosstab query

I am wondering if I can create a complex query in oracle that simulates an Access crosstab. The data is comprised of measurments created from fish scales. The output is based on the water type 'F' - Fresh and 'S' - Salt and the Age of the fish, 5th and 6th characters of the FISH_ID. The 5th charactrer is the Fresh water age and the 6th character is the Salt water age. Any measurements greater than the age is Plus growth. The Annulus Varchar2(1) is a boolean value 0 - False, 1 - True indicating the measurement marker for that age group.

Here is an example of our measurement data:

CREATE TABLE CIRCULUS
(

FISH_ID VARCHAR2(10),
CIRCULUS_NUMBER NUMBER,
WATER_CODE VARCHAR2(1),
ANNULUS VARCHAR2(1),
DISTANCE NUMBER
)

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',1,'F','0',0.0778);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',2,'F','0',0.0256);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',3,'F','0',0.0246);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',4,'F','0',0.042);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',5,'F','0',0.0286);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',6,'F','0',0.0266);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',7,'F','0',0.0297);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',8,'F','0',0.0143);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',9,'F','0',0.0276);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',10,'F','0',0.0205);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',11,'F','0',0.0146);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',12,'F','0',0.0202);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',13,'F','1',0.0246);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',14,'F','0',0.0327);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',15,'F','0',0.0368);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',16,'F','0',0.0247);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',17,'F','0',0.0316);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',18,'F','0',0.0276);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',19,'F','0',0.0225);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',20,'F','0',0.0194);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',21,'F','0',0.042);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',22,'S','0',0.043);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',23,'S','0',0.0583);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',24,'S','0',0.0614);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',25,'S','0',0.0481);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',26,'S','0',0.0491);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',27,'S','0',0.0379);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',28,'S','0',0.0399);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',29,'S','0',0.0379);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',30,'S','0',0.0348);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',31,'S','0',0.0348);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',32,'S','0',0.0286);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',33,'S','0',0.0358);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',34,'S','0',0.0389);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',35,'S','0',0.0368);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',36,'S','0',0.0338);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',37,'S','0',0.0317);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',38,'S','0',0.0276);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',39,'S','0',0.0348);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',40,'S','0',0.0389);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',41,'S','0',0.0358);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',42,'S','0',0.0389);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',43,'S','0',0.0368);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',44,'S','1',0.0409);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',45,'S','0',0.0471);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',46,'S','0',0.0286);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',47,'S','0',0.0358);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',48,'S','0',0.0758);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',49,'S','0',0.0601);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',50,'S','0',0.045);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',51,'S','0',0.0391);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',52,'S','0',0.0348);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',53,'S','0',0.0327);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',54,'S','0',0.0399);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',55,'S','0',0.042);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',56,'S','0',0.0419);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',57,'S','0',0.0338);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',58,'S','0',0.0358);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',59,'S','0',0.0399);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',60,'S','0',0.0368);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',61,'S','0',0.044);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',62,'S','1',0.0553);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',63,'S','0',0.0553);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',64,'S','0',0.0553);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',65,'S','0',0.0409);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',66,'S','0',0.045);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',67,'S','0',0.0491);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',68,'S','0',0.0553);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',69,'S','0',0.0379);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',70,'S','0',0.0542);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',71,'S','0',0.0481);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',72,'S','0',0.0399);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',73,'S','0',0.0409);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',74,'S','0',0.0491);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',75,'S','0',0.0443);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',76,'S','1',0.0611);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',77,'S','0',0.0501);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',78,'S','0',0.043);

insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTANCE) values
('KV0313M030',79,'S','0',0.0481);

This is an example of what the output might look like. Zone Distance is the cumulative measurements for an age group. Circuli count is a count of the circuli in the group and C1 to C30 are column headers displaying the individual measurements.

The possible ages of fish range so there could be FW2, SW4 to SW6, etc.

WATER_AGE	FISH_ID	ZONE_DISTANCE	CIRCULI_COUNT	C1	C2	C3	C4	C5	C6	C7	C8
C9	C10	C11	C12	C13	C14	C15	C16	C17	C18	C19	C20	C21	C22	C23	C24	C25	C26
C27	C28	C29	C30
FW1	KV0313M030	0.2968	12	0.0778	0.0256	0.0246	0.0420	0.0286	0.0266
0.0297	0.0143	0.0276	0.0205	0.0146	0.0202	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000
FWPLUS	KV0313M030	0.2619	9	0.0246	0.0327	0.0368	0.0247	0.0316	0.0276
0.0225	0.0194	0.0420	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000
SW1	KV0313M030	0.8636	22	0.0430	0.0583	0.0614	0.0481	0.0491	0.0379
0.0399	0.0379	0.0348	0.0348	0.0286	0.0358	0.0389	0.0368	0.0338	0.0317
0.0276	0.0348	0.0389	0.0358	0.0389	0.0368	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000
SW2	KV0313M030	0.7540	18	0.0409	0.0471	0.0286	0.0358	0.0758	0.0601
0.0450	0.0391	0.0348	0.0327	0.0399	0.0420	0.0419	0.0338	0.0358	0.0399
0.0368	0.0440	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000
SW3	KV0313M030	0.6706	14	0.0553	0.0553	0.0553	0.0409	0.0450	0.0491
0.0553	0.0379	0.0542	0.0481	0.0399	0.0409	0.0491	0.0443	0.0000
SWPLUS	KV0313M030	0.2023	4	0.0611	0.0501	0.0430	0.0481	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000
0.0000	0.0000	0.0000	0.0000

Anyone out there willing to give this a try? Received on Thu Sep 25 2008 - 11:19:53 CDT