I was explaining recursive CTEs recently, and had to come up with a practical example for the CTE implementation. One of the first use cases that came to mind was the creation of a simple multiplication table. Now that the script is created, I thought that you may also find it useful sometime in the future.

USE tempdb;

GO

–Generate basic mathematical tables using Recursive CTE

DECLARE @inputValue INT = 2;

DECLARE @targetMultiplicationFactor INT = 20;
–Define the CTE to generate a mathematical table

;WITH GenerateTablesCTE (Input, MultiplicationFactor, Result)

AS

(

    –Anchor member

    SELECT @inputValue AS Input,

            1 AS MultiplicationFactor,

            (@inputValue * 1) AS Result

    UNION ALL

    –Recursive member

    SELECT @inputValue AS Input,

            (gtCTE.MultiplicationFactor + 1) AS MultiplicationFactor,

            (@inputValue * (gtCTE.MultiplicationFactor + 1)) AS Result

    FROM GenerateTablesCTE AS gtCTE

    WHERE gtCTE.MultiplicationFactor < @targetMultiplicationFactor

)

SELECT ogtCTE.Input,

        ogtCTE.MultiplicationFactor,

        ogtCTE.Result

FROM GenerateTablesCTE AS ogtCTE;

GO

Here’s the result:

This script can be used to populate a tally/look-up table.

Further Reading

• Multiplication Table [Wikipedia Link]

Until we meet next time,

Be courteous. Drive responsibly.