Generating Multiplication Matrix Dynamically In SQL

Hi All,

After a long time, i come up with some new stuff in T-SQL

ie. Generating Multiplication Matrix Dynamically By Passing a Input as Matrix size ,See below images so that you can easily understand :) .

Script For Above OUTPUT: (Here i am passing the 12 as my input)

DECLARE @k int = 12

IF(@K>0)

BEGIN

DECLARE @FirstPart VARCHAR(max),@SecondPart VARCHAR(MAX) 

SET @FirstPart  = 'DECLARE @i int = 1,@j int = 1,@k int = '+CAST(@k AS VARCHAR)+'  DECLARE @table table ([ ] int'

SET @SecondPart = 'SELECT @i AS [ ], @i*@j AS [1]'  

DECLARE @l INT= 1

WHILE(@l<=@k)

BEGIN

SET @FirstPart = @FirstPart+',['+CAST(@l AS VARCHAR)+'] INT'

IF(@l<@k)

BEGIN

SET @SecondPart = @SecondPart + ',@i*(@j+'+CAST(@l AS VARCHAR)+') AS ['+CAST(@l+1 AS VARCHAR)+']'

END

SET @l= @l+1

END

SET @FirstPart = @FirstPart+')'

DECLARE @ThirdPart VARCHAR(MAX) =

'WHILE (@i<=@k)

BEGIN

INSERT INTO @table '

+@SecondPart+

'SET @i = @i+'+CAST(1 AS VARCHAR)+'

END

SELECT * FROM @table

'

EXEC(@FirstPart+@ThirdPart)

END

ELSE

SELECT 'Sorry We Cant Process This Request' AS Alert

OUTPUT:

 Thanks For Visiting Blog.