这个是我用下面行列互换后得到的。 declare @st_yearmonth varchar(6) declare @ed_yearmonth varchar(6) declare @sql varchar(8000) set @st_yearmonth = '200707' select @ed_yearmonth = datename(yyyy,dateadd(mm,5,convert (datetime,substring(@st_yearmonth,1,4)+right(@st_yearmonth,2)+'01',110))) + datename(mm,dateadd(mm,5,convert (datetime,substring(@st_yearmonth,1,4)+right(@st_yearmonth,2)+'01',110))) set @sql = 'select Vendor_Code,vendor_Name ' select @sql = @sql + ',sum(case yearmonth when ''' + yearmonth + ''' then [po_deduct] else 0 end) [' + yearmonth + ']' from (select distinct yearmonth from SPQuality where yearmonth >= @st_yearmonth and yearmonth<= @ed_yearmonth ) as a select @sql = @sql + ' from SPQuality group by vendor_code,vendor_name ' exec (@sql)SPQuality的资料如下Code Name YearMonth Rcv_Qty Rjt_Qty Score vd0001 HP 200801 100 20 60 vd0001 HP 200707 250 30 60 vd0001 HP 200708 204 2 50 vd0001 HP 200709 40 2 50 vd0001 HP 200710 100 20 60 vd0002 IBM 200708 300 100 60
declare @TabA sysname, @TabB sysname --A表和B表 select @TabA = 'AAAA', @TabB = 'BBBB'declare @YM varchar(6), @I varchar(2), @sql nvarchar(4000) select @YM = 200707, @I = 0declare @Temp table(YM varchar(6)) set nocount on while @I<6 begin insert @Temp select (@YM/100+(@YM%100+@I-1)/12)*100+(@YM%100+@I-1)%12+1 set @I=@I+1 end set nocount offset @I = 0 select @I=@I+1, @sql = case when b.name is null then coalesce(@sql+',a.month', 'a.month') + @I + '=0' else coalesce(@sql+',a.month', 'a.month') + @I + '=b.[' + b.name + ']' end from @Temp a left join syscolumns b on a.YM = b.name where b.id = object_id(@TabB) set @sql = 'update a set ' + @sql + ' from '+ @TabA + ' a join '+ @TabB + ' b on a.Code=b.Code and a.Name=b.Name' --exec(@sql) print @sqlselect @I = 0, @sql = null select @I=@I+1, @sql = case when b.name is null then coalesce(@sql+',month', 'month') + @I + '=0' else coalesce(@sql+',month', 'month') + @I + '=[' + b.name + ']' end from @Temp a left join syscolumns b on a.YM = b.name where b.id = object_id(@TabB) set @sql = 'insert '+ @TabB + ' select Code,Name,' + @sql + ' from '+ @TabA --exec(@sql) print @sql
我明白这段是计算半年的月份。 declare @Temp table(YM varchar(6)) set nocount on while @I<6 begin insert @Temp select (@YM/100+(@YM%100+@I-1)/12)*100+(@YM%100+@I-1)%12+1 set @I=@I+1 end set nocount off 但下面这段我就不明白什么意思了。我把@sql显示出来,总是显示NULL,这是怎么回事?select @I=@I+1, @sql = case when b.name is null then coalesce(@sql+',a.month', 'a.month') + @I + '=0' else coalesce(@sql+',a.month', 'a.month') + @I + '=b.[' + b.name + ']' end from @Temp a left join syscolumns b on a.YM = b.name where b.id = object_id(@TabB) set @sql = 'update a set ' + @sql + ' from '+ @TabA + ' a join '+ @TabB + ' b on a.Code=b.Code and a.Name=b.Name'
现在我已经明白所有语句的含义,但是好像还是有点问题。我要的结果是这样的,而你的结果却是这样的。(以200707为例) update a set a.month1=b.[200707],a.month2=b.[200708],a.month3=b.[200709],a.month4=b.[200710],a.month5=b.[200711],a.month6=b.[200712] from Half_Year a join aa b on a.Vendor_Code=b.Vendor_Code and a.Vendor_Name=b.Vendor_Name 而你的结果却是这样的:update a set a.month1=b.[200708],a.month2=b.[200709],a.month3=b.[200710],a.month4=b.[200801] from Half_Year a join aa b on a.Vendor_Code=b.Vendor_Code and a.Vendor_Name=b.Vendor_Name
declare @st_yearmonth varchar(6)
declare @ed_yearmonth varchar(6)
declare @sql varchar(8000)
set @st_yearmonth = '200707' select @ed_yearmonth = datename(yyyy,dateadd(mm,5,convert (datetime,substring(@st_yearmonth,1,4)+right(@st_yearmonth,2)+'01',110))) + datename(mm,dateadd(mm,5,convert (datetime,substring(@st_yearmonth,1,4)+right(@st_yearmonth,2)+'01',110))) set @sql = 'select Vendor_Code,vendor_Name '
select @sql = @sql + ',sum(case yearmonth when ''' + yearmonth + ''' then [po_deduct]
else 0 end) [' + yearmonth + ']'
from (select distinct yearmonth from SPQuality where yearmonth >= @st_yearmonth and yearmonth<= @ed_yearmonth ) as a
select @sql = @sql + ' from SPQuality group by vendor_code,vendor_name '
exec (@sql)SPQuality的资料如下Code Name YearMonth Rcv_Qty Rjt_Qty Score
vd0001 HP 200801 100 20 60
vd0001 HP 200707 250 30 60
vd0001 HP 200708 204 2 50
vd0001 HP 200709 40 2 50
vd0001 HP 200710 100 20 60
vd0002 IBM 200708 300 100 60
select @TabA = 'AAAA', @TabB = 'BBBB'declare @YM varchar(6), @I varchar(2), @sql nvarchar(4000)
select @YM = 200707, @I = 0declare @Temp table(YM varchar(6))
set nocount on
while @I<6
begin
insert @Temp select (@YM/100+(@YM%100+@I-1)/12)*100+(@YM%100+@I-1)%12+1
set @I=@I+1
end
set nocount offset @I = 0
select @I=@I+1, @sql = case when b.name is null then coalesce(@sql+',a.month', 'a.month') + @I + '=0' else coalesce(@sql+',a.month', 'a.month') + @I + '=b.[' + b.name + ']' end from @Temp a left join syscolumns b on a.YM = b.name where b.id = object_id(@TabB)
set @sql = 'update a set ' + @sql + ' from '+ @TabA + ' a join '+ @TabB + ' b on a.Code=b.Code and a.Name=b.Name'
--exec(@sql)
print @sqlselect @I = 0, @sql = null
select @I=@I+1, @sql = case when b.name is null then coalesce(@sql+',month', 'month') + @I + '=0' else coalesce(@sql+',month', 'month') + @I + '=[' + b.name + ']' end from @Temp a left join syscolumns b on a.YM = b.name where b.id = object_id(@TabB)
set @sql = 'insert '+ @TabB + ' select Code,Name,' + @sql + ' from '+ @TabA
--exec(@sql)
print @sql
declare @Temp table(YM varchar(6))
set nocount on
while @I<6
begin
insert @Temp select (@YM/100+(@YM%100+@I-1)/12)*100+(@YM%100+@I-1)%12+1
set @I=@I+1
end
set nocount off
但下面这段我就不明白什么意思了。我把@sql显示出来,总是显示NULL,这是怎么回事?select @I=@I+1, @sql = case when b.name is null then coalesce(@sql+',a.month', 'a.month') + @I + '=0' else coalesce(@sql+',a.month', 'a.month') + @I + '=b.[' + b.name + ']' end from @Temp a left join syscolumns b on a.YM = b.name where b.id = object_id(@TabB)
set @sql = 'update a set ' + @sql + ' from '+ @TabA + ' a join '+ @TabB + ' b on a.Code=b.Code and a.Name=b.Name'
update a set a.month1=b.[200707],a.month2=b.[200708],a.month3=b.[200709],a.month4=b.[200710],a.month5=b.[200711],a.month6=b.[200712] from Half_Year a join aa b on a.Vendor_Code=b.Vendor_Code and a.Vendor_Name=b.Vendor_Name
而你的结果却是这样的:update a set a.month1=b.[200708],a.month2=b.[200709],a.month3=b.[200710],a.month4=b.[200801] from Half_Year a join aa b on a.Vendor_Code=b.Vendor_Code and a.Vendor_Name=b.Vendor_Name
看看后半年的年份月份对不对,我的是包含当月的,例如指定200707,是从200707-200712,如果你要200708-200801,你把初始增量设为1就行了:
--select @YM = 200707, @I = 0
select @YM = 200707, @I = 1
where b.id = object_id(@TabB) or b.id is NULL (如果不加这句or b.id is NULL 就是有问题的)谢谢你的帮助。