Introduction to the implementation method of drawing the 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 complete SQL code is as follows:
Copy codeThe Code is as follows:
Use tempdb
Go
Set nocount on
Declare @ rows int = 10, -- number of rows, based on actual control
@ 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
(
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 (''),'') 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 (''),'') 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 (''),'') 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 the position division by "/" in the representation.