我这里有一个结构如下的数据表(第一行为表名,下面为数据)(测试数据库备份文件已上传sql server 2000)
"ennm","dscd","ennmcd","sssj","qsl","fdl"
"test1","330283","Qnbfh0yj0118B",2007-11-26 00:00:00,44716.00,223580.00
"test1","330283","Qnbfh0yj0118B",2007-12-20 00:00:00,34928.00,174640.00
"test1","330283","Qnbfh0yj0118B",2008-01-07 00:00:00,40634.00,203170.00
"test2","330283","Qnbfh0yj0122B",2007-11-22 00:00:00,31964.00,159820.00
"test2","330283","Qnbfh0yj0122B",2007-12-17 00:00:00,32940.00,164700.00
"test2","330283","Qnbfh0yj0122B",2007-11-23 00:00:00,31621.00,158105.00
"test2","330283","Qnbfh0yj0122B",2007-11-24 00:00:00,31670.00,158350.00
"test2","330283","Qnbfh0yj0122B",2007-11-25 00:00:00,31601.00,158005.00
"test2","330283","Qnbfh0yj0122B",2007-11-26 00:00:00,32051.00,160255.00
"test2","330283","Qnbfh0yj0122B",2007-11-27 00:00:00,30874.00,154370.00
"test2","330283","Qnbfh0yj0122B",2007-11-28 00:00:00,31792.00,158960.00
"test1","330283","Qnbfh0yj0118B",2008-11-11 00:00:00,42424.00,212120.00
"test2","330283","Qnbfh0yj0122B",2008-11-11 00:00:00,28472.00,142360.00
目前使用以下的存储过程CREATE PROCEDURE P_CreateTableByMonth
@dscd varchar(50),
@year int--生成某月的数据
as
create table #temp(date int)
declare @num int
set @num=1
while(@num<=12)
begin
insert into #temp select @num
set @num=@num+1
end
declare @sql varchar(800)
set @sql='select distinct ennm 名称' select @sql=@sql+',['+cast(date as varchar)+'月]=sum(case datename(mm,sssj) when '''+right('00'+cast(date as varchar),2)+''' then fdl else 0 end)' from #temp
set @sql=@sql+' ,sum(fdl) 当年总量 from [historyQsl] where dscd like ''%'+@dscd+'%'' and datepart(yy,sssj)='+ cast(@year as varchar) +' group by ennm '
exec(@sql)
--生成2007年的数据:
--exec P_CreateTableByMonth 3302, 2007
GO实现了行列转换的统计,
统计效果如下:
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 当年总量
test1 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 4029955.00 6622730.00 10652685.00
test2 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 2559045.00 4817450.00 7376495.00
现在我想在最后一行实现合计的功能
即生成
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 当年总量
test1 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 4029955.00 6622730.00 10652685.00
test2 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 2559045.00 4817450.00 7376495.00
合计 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 6589000.00 11440180.00 18029180.00哪位高手能帮忙解决下,或提供点思路,谢谢!
"ennm","dscd","ennmcd","sssj","qsl","fdl"
"test1","330283","Qnbfh0yj0118B",2007-11-26 00:00:00,44716.00,223580.00
"test1","330283","Qnbfh0yj0118B",2007-12-20 00:00:00,34928.00,174640.00
"test1","330283","Qnbfh0yj0118B",2008-01-07 00:00:00,40634.00,203170.00
"test2","330283","Qnbfh0yj0122B",2007-11-22 00:00:00,31964.00,159820.00
"test2","330283","Qnbfh0yj0122B",2007-12-17 00:00:00,32940.00,164700.00
"test2","330283","Qnbfh0yj0122B",2007-11-23 00:00:00,31621.00,158105.00
"test2","330283","Qnbfh0yj0122B",2007-11-24 00:00:00,31670.00,158350.00
"test2","330283","Qnbfh0yj0122B",2007-11-25 00:00:00,31601.00,158005.00
"test2","330283","Qnbfh0yj0122B",2007-11-26 00:00:00,32051.00,160255.00
"test2","330283","Qnbfh0yj0122B",2007-11-27 00:00:00,30874.00,154370.00
"test2","330283","Qnbfh0yj0122B",2007-11-28 00:00:00,31792.00,158960.00
"test1","330283","Qnbfh0yj0118B",2008-11-11 00:00:00,42424.00,212120.00
"test2","330283","Qnbfh0yj0122B",2008-11-11 00:00:00,28472.00,142360.00
目前使用以下的存储过程CREATE PROCEDURE P_CreateTableByMonth
@dscd varchar(50),
@year int--生成某月的数据
as
create table #temp(date int)
declare @num int
set @num=1
while(@num<=12)
begin
insert into #temp select @num
set @num=@num+1
end
declare @sql varchar(800)
set @sql='select distinct ennm 名称' select @sql=@sql+',['+cast(date as varchar)+'月]=sum(case datename(mm,sssj) when '''+right('00'+cast(date as varchar),2)+''' then fdl else 0 end)' from #temp
set @sql=@sql+' ,sum(fdl) 当年总量 from [historyQsl] where dscd like ''%'+@dscd+'%'' and datepart(yy,sssj)='+ cast(@year as varchar) +' group by ennm '
exec(@sql)
--生成2007年的数据:
--exec P_CreateTableByMonth 3302, 2007
GO实现了行列转换的统计,
统计效果如下:
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 当年总量
test1 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 4029955.00 6622730.00 10652685.00
test2 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 2559045.00 4817450.00 7376495.00
现在我想在最后一行实现合计的功能
即生成
名称 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 当年总量
test1 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 4029955.00 6622730.00 10652685.00
test2 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 2559045.00 4817450.00 7376495.00
合计 .00 .00 .00 .00 .00 .00 .00 .00 .00 .00 6589000.00 11440180.00 18029180.00哪位高手能帮忙解决下,或提供点思路,谢谢!
解决方案 »
- WITH common_table_expression ,临时表为什么只能用一次
- 关于部分词重复的排序问题
- 求sql写法,取得表格中,数据分组后,每组前3名的记录,不知该如何写?
- 如何用FTP建立两个数据库间的同步复制
- 建立索引引起表的死锁,听同事说是有什么什么冲突??
- 一个SQL语句
- 把一个列改为主键且自增的语句怎么错了
- 我用的是D版SQL SERVER2000,想升级到SP3,升级后会有影响吗?听说WIN2000升级到SP3后会对默认安装的OFFICE产生影响。。。请帮助!
- 在没有 sql server 的win98中如何运行delphi+ado+sql server程序???
- SELECT * INTO [Excel 8.0;Database=e:\book1.xls].[sheet1] FROM Customers解答
- sql语句进行多表查分页
- 请大家看一下下面的例子有没有问题??@@error=0 是什么意思???
WITH ROLLUP
@dscd varchar(50),
@year int--生成某月的数据
as
create table #temp(date int)
declare @num int
set @num=1
while(@num<=12)
begin
insert into #temp select @num
set @num=@num+1
end
declare @sql varchar(800)
set @sql='select isnull(ennm,''合计'') 名称' select @sql=@sql+',['+cast(date as varchar)+'月]=sum(case datename(mm,sssj) when '''+right('00'+cast(date as varchar),2)+''' then fdl else 0 end)' from #temp
set @sql=@sql+' ,sum(fdl) 当年总量 from [historyQsl] where dscd like ''%'+@dscd+'%'' and datepart(yy,sssj)='+ cast(@year as varchar) +' group by ennm WITH ROLLUP'
exec(@sql)
--生成2007年的数据:
--exec P_CreateTableByMonth 3302, 2007
GO
原来这里declare @sql varchar(800)被改成800了,到8000就没问题了!
谢谢