寻觅高手写Oracle SQL语句

这个一定能行：

select
name,
sum(case when state=1 then 1 else 0 end) "1",
sum(case when state=2 then 1 else 0 end) "2"
from 表名
group by name;

********************
补充：我写成这样只是为了方便看(这个符合程序开发的原则)，如果非得弄成一行：
select name,sum(case when state=1 then 1 else 0 end) "1",sum(case when state=2 then 1 else 0 end) "2" from 表名 group by name;

实施log：
[TEST@ORA1] SQL>select * from test1;

N STATE
- ----------
A 1
A 1
A 2
B 1
B 2
B 2

6 rows selected.

[TEST@ORA1] SQL>select
2 name,
3 sum(case when state=1 then 1 else 0 end) "1",
4 sum(case when state=2 then 1 else 0 end) "2"
5 from test1
6 group by name;

NAME 1 2
---- ---------- ----------
A 2