原数据示意 编号 日期 数量 0001 200001 20 0001 200002 40 0002 200001 1 0002 200002 29 0003 200001 40 结果示意 A200001 A200002 A200003 ... 0001 20 40 0 0002 1 29 0 0003 40 0 0 过程,不明白来信讨论 CREATE PROCEDURE Zjhl( @begin_Date datetime , --传入查询开始日期 @End_Date datetime, --传入查询结束日期 @tj_bmno varchar(20), --传入要查询的部门编号 @Filename varchar(20) output, --返回表名称 @field_Names varchar(8000) output --返回产品字段标题组 ) AS --Declare @tj_bmno char(20) --set @tj_bmno='001001' declare @Cout_i int Declare @Yf_filed varchar(8000) Declare @yf varchar(2) Declare @nf varchar(200) Declare @updatefiled varchar(8000) Declare @updateTatolHl varchar(8000) Declare @updateTatolDs varchar(8000) Declare @updateTatolXj varchar(8000)set @cout_i=1 set @Yf_filed='' set @updatefiled='' set @updateTatolHl='Sum_hl = ' set @updateTatolDs='Sum_ds= ' set @updateTatolXj='Sum_xj= 'set @nf='' set @field_Names='' --生成字段 if month(@Begin_Date)=1 begin while @cout_i<=12 begin --取 year(@Begin_Date)+[1..12] if @cout_i<10 begin set @yf='0'+convert(char,@cout_i) end else begin set @yf=rtrim(convert(char,@cout_i)) end set @nf=rtrim(convert(char,year(@Begin_Date)))+@yf set @field_Names=@field_Names+rtrim(@nf)+' and ' set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+ 'M'+rtrim(@nf)+'B'+' money null,'+ 'M'+rtrim(@nf)+'C'+' money null,' set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),' set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)' set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)' set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)' set @cout_i=@cout_i+1 end end else begin set @cout_i=month(@Begin_Date) while @cout_i<=12 begin --year(@Begin_Date)+[month(@Begin_Date)..12]... if @cout_i<10 begin set @yf='0'+convert(char,@cout_i) end else begin set @yf=rtrim(convert(char,@cout_i)) end set @nf=rtrim(convert(char,year(@Begin_Date)))+@yf set @field_Names=@field_Names+rtrim(@nf)+' and ' set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+ 'M'+rtrim(@nf)+'B'+' money null,'+ 'M'+rtrim(@nf)+'C'+' money null,' set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),' set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)' set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)' set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)' set @cout_i=@cout_i+1 end set @cout_i=1 while @cout_i<= month(@Begin_Date)-1 begin --取 year(@end_date)+[1..month(@Begin_Date)-1] if @cout_i<10 begin set @yf='0'+convert(char,@cout_i) end else begin set @yf=rtrim(convert(char,@cout_i)) end set @nf=rtrim(convert(char,year(@begin_Date)+1))+@yf set @field_Names=@field_Names+rtrim(@nf)+' and ' set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+ 'M'+rtrim(@nf)+'B'+' money null,'+ 'M'+rtrim(@nf)+'C'+' money null,' set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),' set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)' set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)' set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)' set @cout_i=@cout_i+1 end end Declare @File_I numeric --用于判断 Temp+n 表的存在 Select @File_I=1 select @Filename='Temp1'While exists (select * from sysobjects where id = object_id('[dbo].['+@Filename+']') ) Begin Select @File_I=@File_I + 1 set @Filename='Temp'+CONVERT(char, @File_I) end --生成temp+n 表名称 declare @Field_string varchar(2000) --用于生成temp+n 表 set @Field_string='' set @Field_string='Create Table '+rtrim(@Filename) +'(Cuno varchar(20) null,cuname varchar(20) null,'+rtrim(@Yf_filed)+'bmno varchar(20) null,bmname varchar(20) null,sum_hl money null,sum_Ds money null,sum_xj money null )'; EXEC(@Field_string) --插入地区信息 --在发货单中发生的地区--select @tj_bmno as bmno,(select shortname from sdnet where selfno=@tj_bmno) as bmname,selfno,shortname from sdnet where selfno like rtrim(@tj_bmno)+'_%' and flagInout='1'set @Field_string='INSERT INTO '+ rtrim(@Filename)+' (bmno,bmname,cuno,cuname) select '+ char(39)+rtrim(@tj_bmno)+char(39)+ ' as bmno,(select shortname from sdnet where selfno='+ char(39)+rtrim(@tj_bmno)+char(39)+') as bmname,selfno,shortname from sdnet where selfno like '+char(39)+ rtrim(@tj_bmno)+'_%'+char(39)+ ' and flagInout='+char(39)+'1'+char(39) EXEC(@Field_string) --生成数据游标 Declare Cur_SJ Scroll Cursor For select substring(cuselfno,1,9) as subselfno,skdate,flagInstead,sum(isnull(amt,0)) as amt from ( select bmno,sdnet.selfno as cuselfno ,cuno,substring(convert(char,skdate,102),1,4)+substring(convert(char,skdate,102),6,2) as skdate, amt,flagInstead from skd left outer join sdnet on sdnet.sdnet_no=skd.cuno where (skd.skdate>= @begin_date and skd.skdate<= @end_date and len(ltrim(rtrim(skd.poster)))<>0 and type='收款单' ) ) lsskd where substring(cuselfno,1,6)=@tj_bmno group by substring(cuselfno,1,9),skdate,flagInstead declare @cuno varchar(20) , --地区编号 @skdate varchar(8), --日期 @amt money, --金额 @Instead varchar(10) --代收标志 Declare @field_update varchar(2000) --用于将数据倒入 temp+n表 set @field_update='' open Cur_SJ Select @cout_i=1 While (@cout_i<=@@Cursor_Rows)Begin FETCH ABSOLUTE @cout_i FROM Cur_SJ into @cuno,@skdate,@Instead,@amt begin if rtrim(@Instead)='否'
set @field_update= 'update '+ RTRIM(@Filename) +' set M'+rtrim(@skdate)+'A='+rtrim(ltrim(convert(char,@amt)))+' where cuno='+char(39)+rtrim(@cuno)+char(39) else set @field_update= 'update '+ RTRIM(@Filename) +' set M'+rtrim(@skdate)+'B='+rtrim(ltrim(convert(char,@amt)))+' where cuno='+char(39)+rtrim(@cuno)+char(39) -- select @amt -- select @Instead EXEC(@field_update) end SELECT @cout_i = @cout_i +1 End Close Cur_SJ Deallocate Cur_SJset @updatefiled='update '+rtrim(@Filename)+ ' set '+ substring(rtrim(@updatefiled),1,len(rtrim(@updatefiled))-1) --小计amt EXEC(@updatefiled) --将数据倒入 temp+n表set @updateTatolHl ='update '+rtrim(@Filename)+ ' set '+rtrim(@updateTatolHl) set @updateTatolDs ='update '+rtrim(@Filename)+ ' set '+rtrim(@updateTatolDs) set @updateTatolXj ='update '+rtrim(@Filename)+ ' set '+rtrim(+@updateTatolXj)EXEC(@updateTatolHl) --合计 EXEC(@updateTatolDs) EXEC(@updateTatolXj) --EXEC('select * from ' +@Filename) --select * from temp32
编号 日期 数量
0001 200001 20
0001 200002 40
0002 200001 1
0002 200002 29
0003 200001 40
结果示意
A200001 A200002 A200003 ...
0001 20 40 0
0002 1 29 0
0003 40 0 0
过程,不明白来信讨论
CREATE PROCEDURE Zjhl(
@begin_Date datetime , --传入查询开始日期
@End_Date datetime, --传入查询结束日期
@tj_bmno varchar(20), --传入要查询的部门编号
@Filename varchar(20) output, --返回表名称
@field_Names varchar(8000) output --返回产品字段标题组
) AS
--Declare @tj_bmno char(20)
--set @tj_bmno='001001'
declare @Cout_i int
Declare @Yf_filed varchar(8000)
Declare @yf varchar(2)
Declare @nf varchar(200)
Declare @updatefiled varchar(8000)
Declare @updateTatolHl varchar(8000)
Declare @updateTatolDs varchar(8000)
Declare @updateTatolXj varchar(8000)set @cout_i=1
set @Yf_filed=''
set @updatefiled=''
set @updateTatolHl='Sum_hl = '
set @updateTatolDs='Sum_ds= '
set @updateTatolXj='Sum_xj= 'set @nf=''
set @field_Names=''
--生成字段
if month(@Begin_Date)=1
begin
while @cout_i<=12
begin --取 year(@Begin_Date)+[1..12]
if @cout_i<10
begin
set @yf='0'+convert(char,@cout_i)
end
else
begin
set @yf=rtrim(convert(char,@cout_i))
end
set @nf=rtrim(convert(char,year(@Begin_Date)))+@yf
set @field_Names=@field_Names+rtrim(@nf)+' and '
set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+
'M'+rtrim(@nf)+'B'+' money null,'+
'M'+rtrim(@nf)+'C'+' money null,'
set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),'
set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)'
set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)'
set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)'
set @cout_i=@cout_i+1
end
end
else
begin
set @cout_i=month(@Begin_Date)
while @cout_i<=12
begin --year(@Begin_Date)+[month(@Begin_Date)..12]...
if @cout_i<10
begin
set @yf='0'+convert(char,@cout_i)
end
else
begin
set @yf=rtrim(convert(char,@cout_i))
end
set @nf=rtrim(convert(char,year(@Begin_Date)))+@yf
set @field_Names=@field_Names+rtrim(@nf)+' and '
set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+
'M'+rtrim(@nf)+'B'+' money null,'+
'M'+rtrim(@nf)+'C'+' money null,'
set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),'
set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)'
set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)'
set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)'
set @cout_i=@cout_i+1
end
set @cout_i=1
while @cout_i<= month(@Begin_Date)-1
begin --取 year(@end_date)+[1..month(@Begin_Date)-1]
if @cout_i<10
begin
set @yf='0'+convert(char,@cout_i)
end
else
begin
set @yf=rtrim(convert(char,@cout_i))
end
set @nf=rtrim(convert(char,year(@begin_Date)+1))+@yf
set @field_Names=@field_Names+rtrim(@nf)+' and '
set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+
'M'+rtrim(@nf)+'B'+' money null,'+
'M'+rtrim(@nf)+'C'+' money null,'
set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),'
set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)'
set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)'
set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)'
set @cout_i=@cout_i+1
end
end
Declare @File_I numeric --用于判断 Temp+n 表的存在
Select @File_I=1
select @Filename='Temp1'While exists (select * from sysobjects where id = object_id('[dbo].['+@Filename+']') )
Begin
Select @File_I=@File_I + 1
set @Filename='Temp'+CONVERT(char, @File_I)
end --生成temp+n 表名称
declare @Field_string varchar(2000) --用于生成temp+n 表
set @Field_string=''
set @Field_string='Create Table '+rtrim(@Filename) +'(Cuno varchar(20) null,cuname varchar(20) null,'+rtrim(@Yf_filed)+'bmno varchar(20) null,bmname varchar(20) null,sum_hl money null,sum_Ds money null,sum_xj money null )';
EXEC(@Field_string)
--插入地区信息 --在发货单中发生的地区--select @tj_bmno as bmno,(select shortname from sdnet where selfno=@tj_bmno) as bmname,selfno,shortname from sdnet where selfno like rtrim(@tj_bmno)+'_%' and flagInout='1'set @Field_string='INSERT INTO '+ rtrim(@Filename)+' (bmno,bmname,cuno,cuname)
select '+ char(39)+rtrim(@tj_bmno)+char(39)+ ' as bmno,(select shortname from sdnet where selfno='+ char(39)+rtrim(@tj_bmno)+char(39)+') as bmname,selfno,shortname from sdnet where selfno like '+char(39)+ rtrim(@tj_bmno)+'_%'+char(39)+ ' and flagInout='+char(39)+'1'+char(39)
EXEC(@Field_string)
--生成数据游标
Declare Cur_SJ Scroll Cursor For select substring(cuselfno,1,9) as subselfno,skdate,flagInstead,sum(isnull(amt,0)) as amt from
(
select bmno,sdnet.selfno as cuselfno ,cuno,substring(convert(char,skdate,102),1,4)+substring(convert(char,skdate,102),6,2) as skdate,
amt,flagInstead from skd
left outer join sdnet
on sdnet.sdnet_no=skd.cuno where
(skd.skdate>= @begin_date and
skd.skdate<= @end_date and
len(ltrim(rtrim(skd.poster)))<>0 and
type='收款单'
)
)
lsskd where substring(cuselfno,1,6)=@tj_bmno
group by substring(cuselfno,1,9),skdate,flagInstead
declare @cuno varchar(20) , --地区编号
@skdate varchar(8), --日期
@amt money, --金额
@Instead varchar(10) --代收标志
Declare @field_update varchar(2000) --用于将数据倒入 temp+n表
set @field_update=''
open Cur_SJ
Select @cout_i=1
While (@cout_i<=@@Cursor_Rows)Begin
FETCH ABSOLUTE @cout_i FROM Cur_SJ into @cuno,@skdate,@Instead,@amt
begin
if rtrim(@Instead)='否'
set @field_update= 'update '+ RTRIM(@Filename) +' set M'+rtrim(@skdate)+'A='+rtrim(ltrim(convert(char,@amt)))+' where cuno='+char(39)+rtrim(@cuno)+char(39)
else
set @field_update= 'update '+ RTRIM(@Filename) +' set M'+rtrim(@skdate)+'B='+rtrim(ltrim(convert(char,@amt)))+' where cuno='+char(39)+rtrim(@cuno)+char(39)
-- select @amt
-- select @Instead
EXEC(@field_update)
end
SELECT @cout_i = @cout_i +1
End
Close Cur_SJ
Deallocate Cur_SJset @updatefiled='update '+rtrim(@Filename)+ ' set '+ substring(rtrim(@updatefiled),1,len(rtrim(@updatefiled))-1)
--小计amt
EXEC(@updatefiled) --将数据倒入 temp+n表set @updateTatolHl ='update '+rtrim(@Filename)+ ' set '+rtrim(@updateTatolHl)
set @updateTatolDs ='update '+rtrim(@Filename)+ ' set '+rtrim(@updateTatolDs)
set @updateTatolXj ='update '+rtrim(@Filename)+ ' set '+rtrim(+@updateTatolXj)EXEC(@updateTatolHl) --合计
EXEC(@updateTatolDs)
EXEC(@updateTatolXj)
--EXEC('select * from ' +@Filename)
--select * from temp32