Matrix view

[ibib3]

Hi

I have a table within an application which I am querying via SQLPlus. The table holds information about medical staff and their roles tied to an operation date and theatre eg.

Theatre Date StaffID Role
CA01 28/12/06 LLOSN S1
CA01 28/12/06 WHEP S2
CA02 28/12/06 PRES S1
CA02 28/12/06 CANW S3

what I want is

SessionID Theatre Date S1 S2 S3
CA0128-DEC-06 CA01 28/12/06 LLOSN WHEP
CA0228-DEC-06 CA02 28/12/06 PRES CANW

I thought I could do it with a simple decode statement

select distinct theatre||ses_date as sessionID,
theatre,
ses_date,
decode(s_role,'S1',staffno,'') S1,
decode(s_role,'S2',staffno,'') S2,
decode(s_role,'S3',staffno,'') S3
from
sd_med

but this produces

SessionID Theatre Date S1 S2 S3
CA0128-DEC-06 CA01 28/12/06 LLOSN
CA0128-DEC-06 CA01 28/12/06 WHEP
CA0128-DEC-06 CA01 28/12/06
CA0228-DEC-06 CA02 28/12/06 PRES
CA0228-DEC-06 CA02 28/12/06 CANW
CA0228-DEC-06 CA02 28/12/06

can anyone help me please as this driving me potty!

Thanks

 

[LKBrwnDBA]

Use the MAX() function:

select theatre||ses_date as sessionID,
       theatre,
       ses_date,
       MAX(decode(s_role,'S1',staffno,'')) S1,
       MAX(decode(s_role,'S2',staffno,'')) S2,
       MAX(decode(s_role,'S3',staffno,'')) S3
from
       sd_med 
group by
       theatre||ses_date, theatre, ses_date

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb