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,DISTAN CE) values
('KV0313M030',1,'F','0',0.0778);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',2,'F','0',0.0256);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',3,'F','0',0.0246);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',4,'F','0',0.042);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',5,'F','0',0.0286);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',6,'F','0',0.0266);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',7,'F','0',0.0297);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',8,'F','0',0.0143);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',9,'F','0',0.0276);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',10,'F','0',0.0205);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',11,'F','0',0.0146);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',12,'F','0',0.0202);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',13,'F','1',0.0246);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',14,'F','0',0.0327);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',15,'F','0',0.0368);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',16,'F','0',0.0247);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',17,'F','0',0.0316);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',18,'F','0',0.0276);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',19,'F','0',0.0225);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',20,'F','0',0.0194);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',21,'F','0',0.042);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',22,'S','0',0.043);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',23,'S','0',0.0583);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',24,'S','0',0.0614);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',25,'S','0',0.0481);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',26,'S','0',0.0491);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',27,'S','0',0.0379);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',28,'S','0',0.0399);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',29,'S','0',0.0379);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',30,'S','0',0.0348);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',31,'S','0',0.0348);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',32,'S','0',0.0286);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',33,'S','0',0.0358);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',34,'S','0',0.0389);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',35,'S','0',0.0368);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',36,'S','0',0.0338);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',37,'S','0',0.0317);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',38,'S','0',0.0276);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',39,'S','0',0.0348);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',40,'S','0',0.0389);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',41,'S','0',0.0358);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',42,'S','0',0.0389);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',43,'S','0',0.0368);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',44,'S','1',0.0409);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',45,'S','0',0.0471);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',46,'S','0',0.0286);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',47,'S','0',0.0358);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',48,'S','0',0.0758);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',49,'S','0',0.0601);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',50,'S','0',0.045);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',51,'S','0',0.0391);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',52,'S','0',0.0348);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',53,'S','0',0.0327);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',54,'S','0',0.0399);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',55,'S','0',0.042);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',56,'S','0',0.0419);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',57,'S','0',0.0338);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',58,'S','0',0.0358);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',59,'S','0',0.0399);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',60,'S','0',0.0368);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',61,'S','0',0.044);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',62,'S','1',0.0553);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',63,'S','0',0.0553);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',64,'S','0',0.0553);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',65,'S','0',0.0409);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',66,'S','0',0.045);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',67,'S','0',0.0491);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',68,'S','0',0.0553);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',69,'S','0',0.0379);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',70,'S','0',0.0542);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',71,'S','0',0.0481);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',72,'S','0',0.0399);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',73,'S','0',0.0409);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',74,'S','0',0.0491);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',75,'S','0',0.0443);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',76,'S','1',0.0611);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',77,'S','0',0.0501);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) values
('KV0313M030',78,'S','0',0.043);
insert into CIRCULUS
(FISH_ID,CIRCULUS_NUMBER,WATER_CODE,ANNULUS,DISTAN CE) 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?

On Thu, 25 Sep 2008 09:19:53 -0700 (PDT), Timo
<tim.frawley (AT) alaska (DOT) gov> wrote:

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

On Thu, 25 Sep 2008 09:19:53 -0700 (PDT), Timo
<tim.frawley (AT) alaska (DOT) gov> wrote:

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

On Thu, 25 Sep 2008 09:19:53 -0700 (PDT), Timo
<tim.frawley (AT) alaska (DOT) gov> wrote:

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

On Thu, 25 Sep 2008 09:19:53 -0700 (PDT), Timo
<tim.frawley (AT) alaska (DOT) gov> wrote:

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

On Sep 25, 12:25*pm, sybra... (AT) hccnet (DOT) nl wrote:



Thanks for the tip, no need to be such an ass.

On Sep 25, 12:25*pm, sybra... (AT) hccnet (DOT) nl wrote:



Thanks for the tip, no need to be such an ass.

On Sep 25, 12:25*pm, sybra... (AT) hccnet (DOT) nl wrote:



Thanks for the tip, no need to be such an ass.

On Sep 25, 12:25*pm, sybra... (AT) hccnet (DOT) nl wrote:



Thanks for the tip, no need to be such an ass.

You ASS-ume that I have not already tried anything because I didn’t
post it. Typical of you ASS-umer types is the fact that if all
information isn’t provided up front then the poster deserves a flame
comment regarding their lack of integrity and/or intelligence. It is
ASS-umers like you that make most people not want to post on these
forums because of the response we are most likely to get. Here is an
ASS-umption for you: For you, Senior DBA should stand for Senor DumB
Ass.

As for “volunteering” the only thing you are willing to volunteer is
your arrogant, egotistical vehemence. I didn’t ask you to do anything
you weren’t willing to do, however; you appear to be more than willing
to spout your arrogant ignorance to everyone on the web.

Maybe you were just having a bad day because someone stole your
coloring book. I don’t know what your issue is but I sure do know
that I don’t deserve your comments. What a D**K.

The problem I have with that query is figuring out how to get the
group of data that exists between Booleans. If the guy that put the
table together had thought that through then maybe he would have setup
the table differently. I sure would have. How would you

select * from circulus where circulus_number < (select circulus_number
from circulus where THE FIRST ANNULUS IS 1 and image_name =
'KV0313M030' ) and image_name = 'KV0313M030'

or something like that.

Here I can get the Circulus Number but how can I get it to give only
one value so I can use it as an embedded query?

select image_name,circulus_number,first_value(annulus) over (order by
CIRCULUS_NUMBER) FROM CIRCULUS where annulus=1 and
image_name='KV0313M030'

Of course, that would just give me the first number. What about
between the next two Booleans.

Uhg, I just don’t know enough about the SQL to figure it out.

Maybe Senior Oracle DumB Ass can...