select语句求救！！！

[rebat]

表格式如下：
number bill type
1011 1 a
1011 10 a
1011 10 b
1012 1 a
1013 1 b
1014 1 b

最终结果如下：
number billa billb
1011 11 10
1012 1 0
1013 0 1
1014 0 1

请问各位大虾select语句怎么写?

[linazhu]

你的billb是怎么得来的？

[hawkcai]

select 我不会写，不过这样的视图我会做(假定你的表名字叫aa
create or replace view billa(number,bill)
as select number,sum(bill)
from aa
where aa.type=a
group by number;

create or replace view billb(number,bill)
as select number,sum(bill)
from aa
where aa.type=b
group by number;

create or replace view billab(number,billa,billb)
as select number,bill,0
from billa
union all
select number,0,bill
from billa

create or replace view billab1(number,billa,billb)
as select number,sum(billa),sum(billb)
from billab
group by number;

不知道这样写对不对

[chjcwl]

用什么开发工具？如果是PB只要用交叉表，一个就出来了：
selcet * from afds
行选 number，列选 type ，值选bill,30秒就搞定。
如果是其他就麻烦，两个还好处理，但如果type是动态就一定要建存储过程了。
两个可这样：
select number,sum(case when type='a' then 0 else bill end ) as billa,
sum(case when type='b' then 0 else bill end ) as billb from asdf;

[chjcwl]

不好意思，有点错：
select number,sum(case when type='a' then 0 else bill end ) as billb,
sum(case when type='b' then 0 else bill end ) as billa from asdf
group by number;

[net_steven]

sql:
-----------------------------------------------
select a.number,isnull(b.bill,0) as billa,isnull(c.bill,0) as billb
from (select distinct number from yourtable) a
left join (select number,sum(isnull(bill,0)) as bill from yourtable
where type='a' group by number) b
on a.number=b.number
left join (select number,sum(isnull(bill,0)) as bill from yourtable
where type='b' group by number) c
on a.number=c.number
order by a.number

[zztzz]

http://www.csdn.net/Expert/topic/508/508264.shtm

[net_steven]

只有两种类型的话，chjcwl(PBVC)写法值得学习。

[rebat]

谢谢大家，我试试…………

[rebat]

数据库系统没用了，明天再来，谢谢大家

[蓝天]

select x.number , x.a billa, y.b billb from (select sum(isnull(bill,0)) a ,number from table1 where type ='a' group by number )x full join
( select sum(isnull(bill,0)) b ,number from table1 where type ='b' group by number )y on x.number=y.number

[liuyingbo]

用临时表！！！

[hanyu010]

ORACLE中通过：
SELECT NUMBER,SUM(BILLA) BILLA,SUM(BILLB) BILLB FROM (
SELECT NUMBER,NVL(SUM(BILL),0) BILLA,0 BILLB FROM TABLENAME WHERE TYPE = 'a' GROUP BY TYPE,NUMBER
UNION
SELECT NUMBER,0 BILLA,NVL(SUM(BILL),0) BILLB FROM TABLENAME WHERE TYPE = 'b' GROUP BY TYPE,NUMBER
) GROUP BY NUMBER ORDER BY NUMBER;

[rebat]

用寒玉的方法通过了，谢谢！
也谢谢其他朋友。