oracle11g - Pivot in Oracle 11g

Question

I have searched Stackoverflow about pivot in Oracle 11g but could not find the syntax that works for my situation.

Below is my query (runs in Oracle 11g).

select
age,
gender,
sum(hours) as hours
from table1
group by age, gender

Here is the O/P result

age gender   hours
25  Male     10
55  Female   5
45  Female   12

...And here is my desired O/P result

Age  Male   Female
25   10     0
45    0     12
55    0     5

...And here is my query

select *
from    
(
select
age,
gender,
sum(hours) as hours
from table1
)
pivot (
sum (hours) for gender in (
Male   as 'Male',
Female as 'Female')
)

...And here is the error:

ORA-00931: missing identifier

Could anyone please educate me please?

Answer 1

You were close:

select age, nvl(male, 0) male, nvl(female, 0) female
from    
(
  select age,  gender,  sum(hours) as hours
  from table1
  group by age, gender
)
pivot
(
  sum(hours) for gender in ('Male' as male, 'Female' as female)
);