Pivot query in Oracle

[Fampofo]

Hi all,
my query lloks like this -:
DEPT COURSE STAFF WEEKLY_HRS
HHB A2ACCO Paul G 2
SAM A2BIOL Lois L 5
SAM A2BIOL Alph A 10
HHB A2BUSS Kizito A 3.5
ELE A2COMS Lorr G 3
ELE A2COMS Isabelle P 2
APM A2DNCE Rebek H 3.5

I would like it to be like this:-

DEPT CRSE PAUL LOIS ALPH KIZITO LORR ISABELLA REBEK
HHB A2ACCO 2
HHB A2BUSS 3.5
ELE A2COMS 3 2
APM A2DNCE 3.5
SAM A2BIOL 10 5


It's a pivot table where staff names are displayed as columns with wekly hrs against each staff per course. Can anyone help me as to how to write a query to display this result. Thank you
frank

 

[haste]

I got the result u requested using this statement, however its very expensive n time consuming to write if there are a lot columns:

select DEPT, COURSE, staff, 
decode(staff, 'Rebek H ', weekly_hrs) "Rebek H",
decode(staff, 'Paul G ', weekly_hrs) "Paul G ", 
decode(staff, 'Alph A ', weekly_hrs) "Alph A ", 
decode(staff, 'Lorr G ', weekly_hrs) "Lorr G " 
from test4 
order by dept

Note that the code is not complete in terms of columns. This isnt the most practical solution for me coz its not dynamic.

 

[Fampofo]

Thanks mate for the effort. As you said the columns have been hard coded so one has to go back each time to update the script when there is a new staff.
I will be sticking to your idea for the time being until I can find better solution.
Thanks once again
frank