我用pl/sql,数据库：oracle10g
在一张表里有 storecode 店铺号,storedata 店铺营业额,txdate 日期
怎么通过sql查询，能得到如下效果
店铺号 5/5/2012 6/5/2012 7/5/2012 8/5/2012 9/5/2012 10/5/2012 总计
CN01 2,800.00 2,900.00 3,000.00 3,100.00 3,200.00 3,300.00 18,300.00
CN02 2,900.00 3,000.00 3,100.00 3,200.00 3,300.00 3,400.00 18,900.00
CN03 3,000.00 3,100.00 3,200.00 3,300.00 3,400.00 3,500.00 19,500.00
CN04 3,100.00 3,200.00 3,300.00 3,400.00 3,500.00 3,600.00 20,100.00
CN05 3,200.00 3,300.00 3,400.00 3,500.00 3,600.00 3,700.00 20,700.00
CN06 3,300.00 3,400.00 3,500.00 3,600.00 3,700.00 3,800.00 21,300.00
CN07 3,400.00 3,500.00 3,600.00 3,700.00 3,800.00 3,900.00 21,900.00
CN08 3,500.00 3,600.00 3,700.00 3,800.00 3,900.00 4,000.00 22,500.00
总计 25,200.00 26,000.00 26,800.00 27,600.00 28,400.00 29,200.00 163,200.00
------解决方案--------------------
select
nvl(storecode,'总计'),
sum(case when txdate='5/5/2012' then storedata else 0 end ) as '5/5/2012',
sum(case when txdate='6/5/2012' then storedata else 0 end ) as '6/5/2012',
sum(case when txdate='7/5/2012' then storedata else 0 end ) as '7/5/2012',
sum(case when txdate='8/5/2012' then storedata else 0 end ) as '8/5/2012',
sum(case when txdate='9/5/2012' then storedata else 0 end ) as '9/5/2012',
sum(case when txdate='10/5/2012' then storedata else 0 end ) as '10/5/2012',
sum(storedata ) 总计
from 表名
GROUP BY GROUPING SETS((storecode),())