Oracle 11g 中的数据透视

Pivot in Oracle 11g(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)
);

这篇关于Oracle 11g 中的数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！