Re: SQL Question

From: Steve Davis <spd_at_patrol.i-way.co.uk>
Date: Sun, 19 Apr 1998 11:15:21 GMT
Message-ID: <3539ddf2.0_at_news.i-way.co.uk>

Joseph,

try:

create or replace view my_view as

select t1.resp_id     resp_id,
       t1.sex         sex,
       max(decode(t2.surg_seq, 1, surg_date, null)) surgdate1,
       max(decode(t2.surg_seq, 2, surg_date, null)) surgdate2,
       max(decode(t3.can_seq, 1, tissue, null)) tis1,
       max(decode(t3.can_seq, 2, tissue, null)) tis2,
       max(decode(t3.can_seq, 3, tissue, null)) tis3,
       max(decode(t3.can_seq, 4, tissue, null)) tis4,
       max(decode(t3.can_seq, 5, tissue, null)) tis5
from   t3,
           t2,
           t1
where t2.resp_id_fk = t1.resp_id

and t3.surg_id_fk = t2.surg_id
group by t1.resp_id,

         t1.sex

HTH,
Steve        

Joseph Bonner <jbonner_at_mmg.im.med.umich.edu> wrote:

>I have a research database with a normalized structure. I need to put
>the data into a completely denomalized structure for analysis.

>I have a table that looks like this

>RESP_ID SEX
>10001 MALE
>10002 FEMALE

>one that looks like this....

>RESP_ID_FK SURG_ID SURG_DATE SURG_SEQ
>10001 1 1-jan-1998 1
>10001 2 1-feb-1998 2
>10002 3 1-mar-1998 1

>And another that looks like this ...

>SURG_ID_FK CAN_ID TISSUE can_seq
>1 1 12 1
>1 2 13 2
>1 3 14 3
>2 4 11 4
>2 5 10 5
>3 6 9 1

>I need to create a view that looks like this

>RESP_ID SEX SUGDAT1 SURGDAT2 TIS1 TIS2 TIS3 TIS4
>TIS5
>10001 male 1-jan-1998 1-feb-1999 12 13
>14 11 10
>10002 female 1-mar-1998 NULL 9 NULL NULL
>NULL NULL

>Does anybody have sugestions?

>Thanks in advance
>Joe B>
Received on Sun Apr 19 1998 - 13:15:21 CEST