Draw Yang Hui triangle using SQL

It is interesting to see a post on csdn about the SQL expression for drawing the Yang Hui triangle. Later, I thought about how to calculate C (n, m) = N according to the combination number calculation method of the Yang Hui triangle without using a temporary table! /[M! (N-m)!], .

The following are complete SQL statements:Code:

 Use  Tempdb  Go  Set Nocount On  Declare   @ Rows   Int  =  10 , --  Number of rows, controlled based on actual conditions          @ X  Int  =  1 , @ Y   Int  =  1 , @ SQL   Nvarchar ( Max ), @ Cols   Int   /*  Calculation method based on the combination number of the Yang Hui triangle: C (n, m) = n! /[M! (N-m)!] Draw reference: 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 +  '  )  ' , '''''' ) +  '  ) '  +  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  )  Select   @ SQL  =  Isnull ( @ SQL  +  '  Union all  ' , '' )+ Data_lse From  Cte_1  Exec ( @ SQL )

(【Note]:The current script passed the test on SQL Server 2012.)

:

 

Although this method does not use a temporary table, the biggest disadvantage is that too many rows cannot be set, because the formula (C (n, m) = n! /[M! (N-m)!]) There are n! And M! If too many rows are set, the factorial data is too large and the data type conversion overflows. If you have time, try again to see if you can optimize it by "/" in the expression.