MSSQL杨辉三角

 

declare @SQL varchar(max)		set @SQL='	SELECT'
declare @Select varchar(max)    set @Select=''
declare @INSERT varchar(max)    set @INSERT=''
declare @n int					set @n=10
select @SQL=@SQL+C,@INSERT=@INSERT+I,@Select=@Select+S from 
(   
    select 
	case when number<>@n+1  Then ' CAST(NULL as bigint)  as ['+CAST(number as varchar)+'],' else ' CAST(1 as bigint) as ['+CAST(number as varchar)+'],' end as C,
	'isnull(CAST(['+CAST(number as varchar)+'] as varchar),'''' ),' as  S,
	case when (@n+1-number)>0 Then ' 
	INSERT INTO #t( '+
	(select '['+CAST(a.number as varchar(100))+']'+',' from master..spt_values a where type='P' and a.number BETWEEN @n+2-t.number and @n+t.number for xml path(''))+'['+CAST(@n+1-number as varchar)+'],['+CAST(@n+1+number as varchar)+'],ID)  select '+
	(select '['+CAST(a.number-1 as varchar)+']+['+CAST(a.number+1 as varchar)+'],' from master..spt_values a where type='P' and a.number BETWEEN @n+2-t.number and @n+t.number for xml path(''))+'1,1,'+CAST(number+1 as varchar)+' from #t where ID=(select max(ID) from #t) ' else '' end as I
	from master..spt_values  t where type='P' and number BETWEEN 1 and @n*2+1
)a
set @SQL=@SQL+' 1 as ID INTO #t '+@INSERT+' select '+@Select+' ID  from #t order by ID asc'
exec(@SQL)