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.
raw
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
    (
        [1] 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.
raw
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([1] 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.
raw
DECLARE @s NVARCHAR(MAX)='CREATE PROCEDURE PascalsTriangle3
@Levels INT
AS
BEGIN
	SET NOCOUNT ON
	~o |=2~ |~1 &~2 &~q NVARCHAR(2048)CREATE TABLE #o([1] &)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.



A programming question that I’m sure everyone has been asked at some point during the interview is ‘Pascal’s Triangle’. Besides solving it, nothing would impress your interviewer more than solving it in PowerShell, with support for massively huge numbers. Wikipedia could probably explain this better than I can, but the premise is simple: start with ‘1’, then each row is one element longer than the previous and is made up of the to values above it added together (Except for the values on either end, which are always 1).
raw
function Pascals-Triangle
{
    [CmdLetBinding()]
    param
    (
        # Single parameter is the number of levels
        [Parameter(Mandatory=$true)][ValidateRange(1, [int]::MaxValue)][int]$Levels
    )
    process
    {
        # Create a dummy previous array, just so that we know what is going on.
        $prev = [System.Numerics.BigInteger[]]::new(0)

        # Now for each level
        for($l = 0; $l -lt $Levels; $l++)
        {
            # Create the current working array
            $current = [System.Numerics.BigInteger[]]::new($prev.Length + 1)
            
            # We know for sure that the first and the last element are 1 (This avoids bounds checking in the loop below)
            $current[0] = 1
            $current[$current.Length - 1] = 1

            # Now for all other elements add the previous two together
            for($i = 1; $i -lt $current.Length - 1; $i++)
            {
                $current[$i] = $prev[$i - 1] + $prev[$i]
            }

            # Write out our result
            Write-Output ([string]::Join(' ', $current))

            # Set the previous to the current for the next iteration
            $prev = $current
        }

    }
}

                            
                        
This is most likely the solution that any interviewer would be expecting, because it is by far the simplest to follow. The idea is simple, start with an array of zero length and for every level of the triangle allocate an array which is 1 element longer, and populate it by adding together elements from the previous array. I’m not sure why, but whenever I got this question in college I wanted to do it recursively, which totally screwed me. Also, the reason behind using ‘BigInteger’, is that these number expand very rapidly. Try it out with ‘int’ or even ‘ulong’, and you wont be able to do too many levels. Anyway, the results in powershell end up looking like this.
1
1 1
1 2 1
1 3 3 1
1 4 6 4 1
1 5 10 10 5 1
1 6 15 20 15 6 1
So now, to mix things up, lets do it in a way that doesn’t require us to keep reallocating arrays. This next solution does the whole thing in place, reusing the same array each time. This is not a big deal, because we always know how long the array needs to be when we start. (Length = Levels). We can just go through and add up the values of the previous row each time.
raw
function Pascals-Triangle2
{
    [CmdLetBinding()]
    param
    (
        # Single parameter is the number of levels
        [Parameter(Mandatory=$true)][ValidateRange(1, [int]::MaxValue)][int]$Levels
    )
    process
    {
        # Create our working array
        $row = [System.Numerics.BigInteger[]]::new($Levels)

        # Now for each level
        for($l = 1; $l -le $Levels; $l++)
        {
            # The first index is always 1, and set our previous
            $iPrevious = $row[0] = 1
            for($i = 1; $i -lt $l; $i++)
            {
                # Back up the current value
                $temp = $row[$i]

                # Add in the previous value
                $row[$i] += $iPrevious

                # Swap out the previous
                $iPrevious = $temp
            }

            # Write out our result
            Write-Output ([string]::Join(' ', ($row | select -First $l)))
        }

    }
}
        
                            
                        
And there we have it. So what about a more obfuscated version for those really hard core interviewees? Lets do it. Taking solution 2 and squishing everything together as much as possible we get a horribly confusing solution.
raw
function Pascals-Triangle3
{
    [CmdLetBinding()]
    param
    (
        # Single parameter is the number of levels
        [Parameter(Mandatory=$true)][ValidateRange(1, [int]::MaxValue)][int]$Levels
    )
    process
    {
        ($r=[System.Numerics.BigInteger[]]::new(($l=$Levels)+1))[0]=1
        1..$l|%{"$($r|?{$_-ne0})";$t=1;1..$_|%{$a=$r[$_];$r[$_]+=$t;$t=$a}}
    }
}
                            
                        
Im sure everyone is better at code golf than I am, but this solution already confuses so it should be a good start.



© 2017 - Peter Sulucz | disclaimer

log in