--处理的存储过程
create proc p_qry
@fromMonth int,
@toMonth int
as
declare @s varchar(8000)
set @s=''
select @s=@s+'+isnull('+m+'月,0)'
from(
select id=1,m='一' union all select 2,'二'
union all select 3,'三' union all select 4,'四'
union all select 3,'五' union all select 4,'六'
union all select 3,'七' union all select 4,'八'
union all select 3,'九' union all select 4,'九'
union all select 3,'十一' union all select 4,'十二'
)a where id between @fromMonth and @toMonth
set @s=substring(@s,2,8000)
exec('select *,汇总=('+@s+') from mytable')
go
create proc p_qry
@fromMonth int,
@toMonth int
as
declare @s varchar(8000)
set @s=''
select @s=@s+'+isnull('+m+'月,0)'
from(
select id=1,m='一' union all select 2,'二'
union all select 3,'三' union all select 4,'四'
union all select 3,'五' union all select 4,'六'
union all select 3,'七' union all select 4,'八'
union all select 3,'九' union all select 4,'九'
union all select 3,'十一' union all select 4,'十二'
)a where id between @fromMonth and @toMonth
set @s=substring(@s,2,8000)
exec('select *,汇总=('+@s+') from mytable')
go
create proc p_qry
@fromMonth int,
@toMonth int
as
declare @s varchar(8000)
set @s=''
select @s=@s+'+isnull('+m+'月,0)'
from(
select id=1,m='一' union all select 2,'二'
union all select 3,'三' union all select 4,'四'
union all select 5,'五' union all select 6,'六'
union all select 7,'七' union all select 8,'八'
union all select 9,'九' union all select 10,'九'
union all select 11,'十一' union all select 12,'十二'
)a where id between @fromMonth and @toMonth
set @s=substring(@s,2,8000)
exec('select *,汇总=('+@s+') from mytable')
create table mytable(部门 int,车数 int,一月 int,二月 int,三月 int,四月 int,五月 int,十二月 int)
insert mytable select 1,100,5,5,6,7,9,10
union all select 2,60,1,9,5,1,5, 5
union all select 3,90, 5,5,5,6,7,4
go--处理的存储过程
create proc p_qry
@fromMonth int,
@toMonth int
as
declare @s varchar(8000)
set @s=''
select @s=@s+'+isnull('+m+'月,0)'
from(
select id=1,m='一' union all select 2,'二'
union all select 3,'三' union all select 4,'四'
union all select 5,'五' union all select 6,'六'
union all select 7,'七' union all select 8,'八'
union all select 9,'九' union all select 10,'九'
union all select 11,'十一' union all select 12,'十二'
)a where id between @fromMonth and @toMonth
set @s=substring(@s,2,8000)
exec('select *,汇总=('+@s+') from mytable')
go--调用
exec p_qry 2,5
go--删除测试
drop table mytable
drop proc p_qry/*--测试结果
部门 车数 一月 二月 三月 四月 五月 十二月 汇总
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 100 5 5 6 7 9 10 27
2 60 1 9 5 1 5 5 20
3 90 5 5 5 6 7 4 23(所影响的行数为 3 行)
--*/
@fromMonth int,
@toMonth int
as
declare @s varchar(8000),@fd varchar(8000)
select @s='',@fd=''
select @s=@s+'+isnull('+m+'月,0)'
,@fd=@fd+','+m+'月'
from(
select id=1,m='一' union all select 2,'二'
union all select 3,'三' union all select 4,'四'
union all select 5,'五' union all select 6,'六'
union all select 7,'七' union all select 8,'八'
union all select 9,'九' union all select 10,'九'
union all select 11,'十一' union all select 12,'十二'
)a where id between @fromMonth and @toMonth
set @s=substring(@s,2,8000)
exec('select 部门,车数'+@fd+',汇总=('+@s+') from mytable')
go--调用
exec p_qry 2,4
create table mytable(部门 int,车数 int,一月 int,二月 int,三月 int,四月 int,五月 int,十二月 int)
insert mytable select 1,100,5,5,6,7,9,10
union all select 2,60,1,9,5,1,5, 5
union all select 3,90, 5,5,5,6,7,4
go--处理的存储过程
create proc p_qry
@fromMonth int,
@toMonth int
as
declare @s varchar(8000),@fd varchar(8000)
select @s='',@fd=''
select @s=@s+'+isnull('+m+'月,0)'
,@fd=@fd+','+m+'月'
from(
select id=1,m='一' union all select 2,'二'
union all select 3,'三' union all select 4,'四'
union all select 5,'五' union all select 6,'六'
union all select 7,'七' union all select 8,'八'
union all select 9,'九' union all select 10,'九'
union all select 11,'十一' union all select 12,'十二'
)a where id between @fromMonth and @toMonth
set @s=substring(@s,2,8000)
exec('select 部门,车数'+@fd+',汇总=('+@s+') from mytable')
go--调用
exec p_qry 2,4
go--删除测试
drop table mytable
drop proc p_qry/*--测试结果部门 车数 二月 三月 四月 汇总
----------- ----------- ----------- ----------- ----------- -----------
1 100 5 6 7 18
2 60 9 5 1 15
3 90 5 5 6 16(所影响的行数为 3 行)
--*/