Oracle 11g 中的数据透视
我在 Stackoverflow 中搜索了有关 Oracle 11g 中枢轴的信息，但找不到适合我情况的语法.
I have searched Stackoverflow about pivot in Oracle 11g but could not find the syntax that works for my situation.
以下是我的查询(在 Oracle 11g 中运行).
Below is my query (runs in Oracle 11g).
select
age,
gender,
sum(hours) as hours
from table1
group by age, gender
这是O/P结果
age gender hours
25 Male 10
55 Female 5
45 Female 12
...这是我想要的 O/P 结果
...And here is my desired O/P result
Age Male Female
25 10 0
45 0 12
55 0 5
...这是我的查询
select *
from
(
select
age,
gender,
sum(hours) as hours
from table1
)
pivot (
sum (hours) for gender in (
Male as 'Male',
Female as 'Female')
)
...这是错误:
ORA-00931: missing identifier
谁能教教我?
推荐答案
你很亲密:
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)
);
相关文章