TSQL Fizz Buzz

Sep27.17 12AM

Here’s another journey through the world of simple programming problems… FizzBuzz, T-SQL edition. So just like the rest of the Fizz Buzz solutions. The idea is simple, for every number 1-100, if it’s divisible by 3 print ‘fizz’, if it’s divisible by 5 print ‘buzz’, if both print ‘fizzbuzz’, otherwise print the number. So here is a basic SQL solution:
raw
WITH ctCounter AS
(
	SELECT 1 AS Number

	UNION ALL

	SELECT Number + 1 AS Number
	FROM ctCounter
	WHERE Number < 100
)
SELECT ISNULL(NULLIF(CONCAT(fizz.f, buzz.b), ''), c.Number) FROM ctCounter c
LEFT JOIN (VALUES('fizz')) fizz(f)
  ON c.Number % 3 = 0
LEFT JOIN (VALUES('buzz')) buzz(b)
  ON c.Number % 5 = 0
                            
                        
This is a common table expression, which generates a row for each number between 1 and 100. Then joins with the values ‘fizz’ and ‘buzz’. After that, it concatenates the fizz and buzz together, and if it still results in an empty string, selects the original number. The NULLIF function is useful for that, because it is basically the inverse of ISNULL. If the first parameter matches the second parameter, it returns null, otherwise returns the first parameter.



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.



How to create an arbitrary list in SQL. Its a little easier said than done. Heres a code snippet which will do just that. The biggest power of this, is running queries to figure out what kind of ranges do not have any data. For example, consider a table of values between 1 and 10000. If someone were to ask which unique set of numbers existed in the table, that would be pretty easy. But if someone were to ask which set of number did not exist in the table, that query would be pretty tough. You have nothing to join against.
raw
DECLARE @lowInclusive  INT = 3
DECLARE @highInclusive INT = 55

-- Declare a common table expression
;WITH generator AS
(
    -- In the base case, just select our first number as a row
    SELECT
        number
    FROM (VALUES(@lowInclusive)) AS base(number)

    UNION ALL

    -- Now select recursively from the common table until we reach our high number
    SELECT
        number + 1
    FROM generator
    WHERE number < @highInclusive
)
SELECT * FROM generator

                            
                        
There you have it. A row list of numbers. You can expand this do work with things like dates as well.
raw
DECLARE @lowInclusive  Date = GETUTCDATE()
DECLARE @highInclusive Date = DATEADD(DAY, 30, @lowInclusive)

-- Declare a common table expression
;WITH generator AS
(
    -- In the base case, just select our first number as a row
    SELECT
        d
    FROM (VALUES(@lowInclusive)) AS base(d)

    UNION ALL

    -- Now select recursively from the common table until we reach our high number
    SELECT
        DATEADD(DAY, 1, d)
    FROM generator
    WHERE d < @highInclusive
)
SELECT * FROM generator

                            
                        
There you have it. The result ends up looking like this
day
2017-08-12
2017-08-13
2017-08-14
2017-08-15
2017-08-16
2017-08-17
2017-08-18
2017-08-19
2017-08-20
2017-08-21
2017-08-22
2017-08-23
2017-08-24
2017-08-25
2017-08-26
2017-08-27
2017-08-28
2017-08-29
2017-08-30
2017-08-31
2017-09-01
2017-09-02
2017-09-03
2017-09-04
2017-09-05
2017-09-06
2017-09-07
2017-09-08
2017-09-09
2017-09-10
2017-09-11
And now you can easily join to figure out which days of the month someone forgot to pay their bills, or whatever.



© 2017 - Peter Sulucz | disclaimer

log in