复制代码 代码如下:

use tempdb
go
set nocount on
declare @rows int=10, --行数，根据实际来控制
        @x int=1,@y int=1,@sql nvarchar(max),@cols int

/*
根据杨辉三角的组合数计算方法：C(n,m)=n!/[m!(n-m)!]进行绘制
参照：http://baike.baidu.com/view/7804.htm
*/

set @cols=@rows*2-1
;with cte_n as
(
    select r from (select row_number() over(order by a.object_id) as r from sys.all_columns a ) x where r<=@rows*2
)
,cte_1 as(select n.r,b.data_lse
            from cte_n n
                cross apply(select \'select \'+stuff((select \',rtrim(\'+isnull(F1.v+\'/((\'+F2.v+\')*\'+F3.v+\')\',\'\'\'\'\'\') +\') as \'+quotename(isnull(nullif((m.r +(@rows-n.r)+(m.r-1)*1)%@cols,0),@cols))
                                from cte_n m
                                    outer apply(select stuff((select \'*\'+rtrim(i.r) from cte_n i where i.r<=isnull((nullif(n.r-1,0)),1) for xml path(\'\')),1,1,\'\') as v
                                        ) F1
                                    outer apply(select stuff((select \'*\'+rtrim(i.r) from cte_n i where i.r<=isnull((nullif(m.r-1,0)),1) for xml path(\'\')),1,1,\'\') as v
                                        ) F2
                                    outer apply(select stuff((select \'*\'+rtrim(i.r) from cte_n i where i.r<=isnull((nullif(n.r-m.r,0)),1) for xml path(\'\')),1,1,\'\') as v
                                        ) F3
                                where m.r<@rows*2
                                order by isnull(nullif((m.r +(@rows-n.r)+(m.r-1)*1)%@cols,0),@cols) asc
                                for xml path(\'\')                                   
                                ),1,1,\'\') as data_lse
                            )b
                where n.r <=@rows
            )