Prev: Can this be done with a trigger?
Next: stuck on Clone database creation step
From: Timo on 25 Sep 2008 12:19

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?
From: sybrandb on 25 Sep 2008 16:25
On Thu, 25 Sep 2008 09:19:53 -0700 (PDT), Timo
<tim.frawley(a)alaska.gov> wrote:

>Anyone out there willing to give this a try?

I assume your boss, Sarah Palin, is going to pay the person doing your
work writing this query for an unknown Oracle version, where you
didn't attempt anytning at all (not even writing down the Oracle
version)?
This is a volunteer forum. Probably Alaska does have a dictionary
somewhere allowing you to look up the word 'volunteer' and get ashamed
of yourself.
After that, please look up 'Pivot table' in the online Oracle
documentation.

--

Sybrand Bakker
Senior Oracle DBA
From: KrunoG on 27 Sep 2008 14:19

<sybrandb(a)hccnet.nl> wrote in message
news:2msnd4dlu814ocdnm5j9s9as7dgjpk00jb(a)4ax.com...
> On Thu, 25 Sep 2008 09:19:53 -0700 (PDT), Timo
> <tim.frawley(a)alaska.gov> wrote:
>
>>Anyone out there willing to give this a try?
>
> I assume your boss, Sarah Palin, is going to pay the person doing your
> work writing this query for an unknown Oracle version, where you
> didn't attempt anytning at all (not even writing down the Oracle
> version)?
> This is a volunteer forum. Probably Alaska does have a dictionary
> somewhere allowing you to look up the word 'volunteer' and get ashamed
> of yourself.
> After that, please look up 'Pivot table' in the online Oracle
> documentation.
>
> --
>
> Sybrand Bakker
> Senior Oracle DBA

You should really try to control your behaviour a bit.
Some people are still at the begining of Oracle story. Concerning your
reccuring comments about 'you doing a work for others benefit', well that's
just boring so please stop that for the sake of this group (or Oracle for
what it stands in general).

BR
---
OCM


 | 
Pages: 1
Prev: Can this be done with a trigger?
Next: stuck on Clone database creation step