create table biao1(nname VARCHAR(128),bb datetime,mmoney NUMeric(20))
insert into biao1 select N'张三','2005-01-01',111.11
union all select N'张三','2005-01-02',111.11
union all select N'李四','2005-12-02',111.11
GO
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', SUM(Case Month([bb]) When '+Rtrim(@I)+' Then mmoney Else 0 End) As ['+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S=N'Select IsNull(nname,N''总计'') As name '+@S+N',SUM([mmoney]) As 总计 From biao1 Group By nname With Rollup'
--Select @S
EXEC(@S)
GO
上面实例是sql2000的，求助转变为在plsql下能运行oracle数据
------解决方案--------------------
这是固定的12个月份字段对吧
------解决方案--------------------
with t as
(select 'zhangsan' nm, '2014-01-01' dt, 100 amt
from dual
union all
select 'lisi' nm, '2014-03-01' dt, 100 amt
from dual
union all
select 'lisi' nm, '2014-03-01' dt, 100 amt
from dual
union all
select 'wangwu' nm, '2014-05-01' dt, 100 amt
from dual)
select distinct nm,
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '01') "01",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '02') "02",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '03') "03",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '04') "04",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '05') "05",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '06') "06",
(select sum(amt)
from t t2
where t2.nm = t1.nm
and substr(t2.dt, 6, 2) = '07') "07",