Looking around online, I could not find a great example of how to draw Pascal’s Triangle in SQL. The solution is quite cool in SQL, because management studio prints out a nice grid for you. Here are three examples. The first one is simple and well documented, the second one is a little more squished, and the third one is a complete dumpster fire that I got too by looking up how to code golf in SQL.
CREATE PROCEDURE PascalsTriangle @Levels INT AS BEGIN SET NOCOUNT ON -- Declare variables DECLARE @counter INT = 2 DECLARE @innerCount INT DECLARE @tmp DECIMAL(38,0) DECLARE @tmp2 DECIMAL(38,0) DECLARE @dquery NVARCHAR(2048) -- Create the results table with its one column CREATE TABLE #result (  DECIMAL(38,0) ) INSERT INTO #result VALUES (1) -- For every level beyond 1 WHILE(@counter <= @Levels) BEGIN -- Add another column to to the table EXEC('ALTER TABLE #result ADD [' + @counter + '] DECIMAL(38,0)') -- Select the previous row into a temp table SELECT * INTO #row FROM #result ORDER BY 1 OFFSET (@counter - 2) ROWS FETCH NEXT 1 ROWS ONLY -- Re-initialize the temp variables, and loop counter SET @tmp = 1 SET @tmp2 = 1 SET @innerCount = 2 -- For every column beyond 1 WHILE(@innerCount <= @counter) BEGIN -- Create a query which selects the current value of the cell to back it up. SET @dquery = 'SELECT @t = [' + CAST(@innerCount AS NVARCHAR(10)) + '] FROM #row' EXEC sp_executesql @dquery, N'@t DECIMAL(38,0) OUTPUT', @t = @tmp2 OUTPUT -- Set the value of this cell in this row to that + @tmp EXEC('UPDATE #row SET [' + @innerCount + '] = ISNULL([' + @innerCount + '], 0) + ' + @tmp) -- Reset the temp variable SET @tmp = @tmp2 -- Increment the count SET @innerCount += 1 END -- Insert the row into result INSERT INTO #result SELECT * FROM #row -- Increment the counter SET @counter += 1 -- Drop the row temp table DROP TABLE #row END -- Output the result SELECT * FROM #result DROP TABLE #result END
There is another post in PowerShell which does a much better job actually explaining details about how this triangle works. But long story short, every item is the sum of the two above it added together. So now let’s make the script a little smaller.
CREATE PROCEDURE PascalsTriangle2 @Levels INT AS BEGIN SET NOCOUNT ON DECLARE @o INT=2 DECLARE @ INT DECLARE @1 DECIMAL(38,0) DECLARE @2 DECIMAL(38,0) DECLARE @q NVARCHAR(2048) CREATE TABLE #o( DECIMAL(38,0)) INSERT #o VALUES(1) o: EXEC('ALTER TABLE #o ADD['+@o+']DECIMAL(38,0)') SELECT *INTO #r FROM #o ORDER BY 1 OFFSET(@o-2)ROWS FETCH NEXT 1 ROWS ONLY SET @1 = 1 SET @2 = 1 SET @ = 2 i: SET @q = CONCAT('DECLARE @ TABLE (val DECIMAL(38,0));UPDATE #r SET [',@,']=ISNULL([',@,'], 0)+',@1,'OUTPUT deleted.[',@,']INTO @;SET @t=(SELECT *FROM @)') EXEC sp_executesql @q,N'@t DECIMAL(38,0) OUTPUT',@t=@2 OUTPUT SET @1 = @2 SET @ += 1 IF @ <= @o GOTO i INSERT #o SELECT * FROM #r SET @o += 1 DROP TABLE #r IF @o <= @Levels GOTO o SELECT * FROM #o DROP TABLE #o END
As you can see, I removed all the pesky comments which were just getting in the way of looking small. Also, the while loops were all turned into GOTO loops to take up a little less space. The variable names were shortened a little as well.
DECLARE @s NVARCHAR(MAX)='CREATE PROCEDURE PascalsTriangle3 @Levels INT AS BEGIN SET NOCOUNT ON ~o |=2~ |~1 &~2 &~q NVARCHAR(2048)CREATE TABLE #o( &)INSERT #o VALUES(1) o:EXEC(''ALTER TABLE #o ADD[''+@o+'']&'')$|O #r ^#o ORDER BY 1 OFFSET(@o-2)ROWS FETCH NEXT 1 ROWS ONLY!1 = 1!2 = 1! = 2 i:!q = CONCAT(''~ TABLE (val &);UPDATE #r SET ['',@,'']=ISNULL(['',@,''], 0)+'',@1,''OUTPUT deleted.['',@,'']|O @;!t=($^@)'')EXEC sp_executesql @q,N''@t & OUTPUT'',@t=@2 OUTPUT!1 = @2!+=1IF @ <= @o GOTO i INSERT #o$ ^#r!o += 1%#r IF @o <= @Levels goto o$ ^#o%#o END' SELECT @s=REPLACE(@s,LEFT(i,1),SUBSTRING(i,2,20)) FROM(VALUES('~ DECLARE @'),('! SET @'),('& DECIMAL(38,0)'),('% DROP TABLE '),('$ SELECT *'),('^ FROM '),('| INT'))a(i) EXEC(@s)
Now there’s the real beast. It does a bunch of replacements on a string, to make some longer valid SQL, then executes the SQL dynamically to produce the stored procedure. That’s some production quality code.