#0338 – SQL Server – Recursive CTE – Script to generate a simple multiplication table


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:


image


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.

Advertisements

One thought on “#0338 – SQL Server – Recursive CTE – Script to generate a simple multiplication table

  1. Jeff Moden

    Ooooo, be careful. Using rCTEs to count is as bad as or worse than any While loop to accomplish the same thing. They’re real resource hogs, to boot. Please see the following article before you even think of using an rCTE that counts or does similar RBAR things. And, yeah… it’s RBAR as you’ll see in the article.
    [link text][1]

    [1]: http://www.sqlservercentral.com/articles/T-SQL/74118/

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s