declare @SQL varchar(max) set @SQL='SELECT' declare @INSERT varchar(max) set @INSERT='' declare @Update varchar(max) set @Update='' declare @n int set @n=10 select @SQL=@SQL+C,@INSERT=@INSERT+I from ( select case when number<>@n+1 Then ' NULL as ['+CAST(number as varchar)+'],' else ' 1 as ['+CAST(number as varchar)+'],' end as C, case when (@n+1-number)>0 Then ' INSERT INTO #t(['+CAST(@n+1-number as varchar)+'],['+CAST(@n+1+number as varchar)+'],ID) VALUES(1,1,'+CAST(number+1 as varchar)+')' else '' end as I from master..spt_values where type='P' and number BETWEEN 1 and @n*2+1 )a select @Update=@Update+U from ( select ('['+CAST(number as varchar)+']=(case when ['+CAST(number as varchar)+']=1 then 1 else (select top 1 ['+CAST(number-1 as varchar)+']+['+CAST(number+1 as varchar)+'] from #t a where a.ID=#t.ID-1) end), '+'') as U from master..spt_values where type='P' and number BETWEEN 3 and @n*2-1 )aa set @Update=' declare @i int set @i='+CAST(@n as varchar)+' while(@i>0) begin Update #T set '+@Update+'ID=ID set @i=@i-1 end' set @SQL=@SQL+' 1 as ID INTO #t alter table #t alter column ['+CAST(@n+1 as varchar)+'] bigint '+@INSERT+' '+@Update+' select * from #t order by ID' exec(@SQL) 晚上瘦身了下
declare @SQL varchar(max) set @SQL='SELECT'
declare @INSERT varchar(max) set @INSERT=''
declare @Update varchar(max) set @Update=''
declare @n int set @n=10
select @SQL=@SQL+C,@INSERT=@INSERT+I from
(
select case when number<>@n+1 Then ' NULL as ['+CAST(number as varchar)+'],' else ' 1 as ['+CAST(number as varchar)+'],' end as C,
case when (@n+1-number)>0 Then ' INSERT INTO #t(['+CAST(@n+1-number as varchar)+'],['+CAST(@n+1+number as varchar)+'],ID) VALUES(1,1,'+CAST(number+1 as varchar)+')' else '' end as I
from master..spt_values where type='P' and number BETWEEN 1 and @n*2+1
)a
select @Update=@Update+U from
(
select ('['+CAST(number as varchar)+']=(case when ['+CAST(number as varchar)+']=1 then 1 else (select top 1 ['+CAST(number-1 as varchar)+']+['+CAST(number+1 as varchar)+'] from #t a where a.ID=#t.ID-1) end), '+'') as U
from master..spt_values where type='P' and number BETWEEN 3 and @n*2-1
)aa
set @Update=' declare @i int set @i='+CAST(@n as varchar)+' while(@i>0) begin Update #T set '+@Update+'ID=ID set @i=@i-1 end'
set @SQL=@SQL+' 1 as ID INTO #t alter table #t alter column ['+CAST(@n+1 as varchar)+'] bigint '+@INSERT+' '+@Update+' select * from #t order by ID'
exec(@SQL)
晚上瘦身了下