This is my current code that generate 2 multiplication tables starting from a given number:
CREATE
OR
ALTER PROCEDURE dbo.math_tables (@x INT = 1)
AS
BEGIN
DECLARE @y INT = 1
DECLARE @ctr INT = 0
BEGIN
WHILE @ctr <= 2
BEGIN
WHILE @y <= 10
BEGIN
PRINT ltrim(str(@x)) ' x ' ltrim(str(@y)) ' = ' ltrim(str(@x * @y))
SET @y = 1
END
SET @x = 1
SET @y = 1
SET @ctr = 1
PRINT '---------------------------'
END
END
END
GO
EXECUTE dbo.math_tables 5 /* Starting table */
GO
The results currently look like this one after the other:
5 x 1 = 5
5 x 2 = 10
5 x 3 = 15
5 x 4 = 20
5 x 5 = 25
5 x 6 = 30
5 x 7 = 35
5 x 8 = 40
5 x 9 = 45
5 x 10 = 50
---------------------------
6 x 1 = 6
6 x 2 = 12
6 x 3 = 18
6 x 4 = 24
6 x 5 = 30
6 x 6 = 36
6 x 7 = 42
6 x 8 = 48
6 x 9 = 54
6 x 10 = 60
---------------------------
But I want it to look like this. Basically I'm trying to start from a new column every-time @ctr goes up by one:
5 x 1 = 5 6 x 1 = 6
5 x 2 = 10 6 x 2 = 12
5 x 3 = 15 6 x 3 = 18
5 x 4 = 20 6 x 4 = 24
5 x 5 = 25 6 x 5 = 30
5 x 6 = 30 6 x 6 = 36
5 x 7 = 35 6 x 7 = 42
5 x 8 = 40 6 x 8 = 48
5 x 9 = 45 6 x 9 = 54
5 x 10 = 50 6 x 10 = 60
Please don't ask why... I'm trynna learn query result formatting for readability ;D
CodePudding user response：
As per your requirement, you need to go with Temp/physical table. Choose whatever you can choose it. else you need to write some string manipulation operations more as per your result set.
I've modified the query as per my understanding. and confirm that I'm going with table approaches.
DECLARE @x INT = 5
DECLARE @y INT = 1
DECLARE @ctr INT = 0
DECLARE @Value NVARCHAR(200)
DECLARE @SQL NVARCHAR(MAX)
CREATE Table #Matrix
(
Id INT IDENTITY(1,1)
)
BEGIN
WHILE @ctr <= 2
BEGIN
EXEC('ALTER TABLE #Matrix ADD COL_' @ctr ' NVARCHAR(200)')
WHILE @y <= 10
BEGIN
SET @Value = ltrim(str(@x)) ' x ' ltrim(str(@y)) ' = ' ltrim(str(@x * @y))
IF(@ctr>0)
BEGIN
SET @SQL = CONCAT('UPDATE #Matrix SET [COL_',CAST(@ctr AS NVARCHAR),']=''',@Value,''' WHERE Id = ',@y)
END
ELSE
BEGIN
SET @SQL = CONCAT('INSERT INTO #Matrix([COL_',CAST(@ctr AS NVARCHAR),']) VALUES(''',@Value,''')')
END
EXEC(@SQL)
SET @y = 1
END
SET @x = 1
SET @y = 1
SET @ctr = 1
END
END
SELECT * FROM #Matrix
DROP TABLE #Matrix
Output
In the query, I added one table namely #Matrix In the table, we are adding the columns once while the loop executing.
Once a column is added to the table we insert/update the data into a table.
CodePudding user response：
As @Lamu suggested, here's a way to do it without loops or recursion. In 2022, you can replace the "Tally" cte with GENERATE_SERIES() or, if you have one, you can use you a Numbers/Tally table or set based function like Itzik Ben-Gan's "GetNums" function or my "fnTally" function.
And, a little integer division and Modulo do the "positioning" of the data. Remember that Modulus returns the "Remainder" for division and that when both the Dividend and the Divisor are integers, so will be the Quotient and the Remainder.
WITH
cteTally AS
(--==== Generate a psuedo Tally Table from 1 to 11.
-- We'll have a separating bar show up at 11.
SELECT TOP (11)
N = ROW_NUMBER() OVER (ORDER BY @@SPID) --Used for both the Multiplicand and Multiplier
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
,cteMultiply AS
(--==== Create the display value for each product combination and a row# to derive Grp, Col, and Row from.
-- The CROSS JOIN is also known as a "Cartesian Product" or "Square Join", like a times table.
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY t1.N,t2.N)-1
,DisplayValue = CONCAT(CONVERT(CHAR(3),t1.N),'x ',CONVERT(CHAR(3),t2.N),'= ',t1.N*t2.N)
FROM cteTally t1 --Multiplicand
CROSS JOIN cteTally t2 --Multiplier
)--==== This is what is known as a "CROSSTAB".
SELECT Col1 = MAX(CASE
WHEN Row = 10 THEN REPLICATE('-',13)
WHEN Col = 0 THEN DisplayValue
END)
,Col2 = MAX(CASE
WHEN Row = 10 THEN REPLICATE('-',13)
WHEN Col = 1 THEN DisplayValue
END)
FROM cteMultiply m
CROSS APPLY (VALUES(RowNum/22, RowNum/11%2,RowNum))ca(Grp,Col,Row) --"DRY" it out.
WHERE RowNum < 110
GROUP BY ca.Grp, ca.Row
ORDER BY ca.Grp, ca.Row
;
I got a bit carried away with doing the whole 10*10 thing. The code should be fairly easy to modify to suit. Here's the result set from that...
Col1 Col2
1 x 1 = 1 2 x 1 = 2
1 x 2 = 2 2 x 2 = 4
1 x 3 = 3 2 x 3 = 6
1 x 4 = 4 2 x 4 = 8
1 x 5 = 5 2 x 5 = 10
1 x 6 = 6 2 x 6 = 12
1 x 7 = 7 2 x 7 = 14
1 x 8 = 8 2 x 8 = 16
1 x 9 = 9 2 x 9 = 18
1 x 10 = 10 2 x 10 = 20
------------- -------------
3 x 1 = 3 4 x 1 = 4
3 x 2 = 6 4 x 2 = 8
3 x 3 = 9 4 x 3 = 12
3 x 4 = 12 4 x 4 = 16
3 x 5 = 15 4 x 5 = 20
3 x 6 = 18 4 x 6 = 24
3 x 7 = 21 4 x 7 = 28
3 x 8 = 24 4 x 8 = 32
3 x 9 = 27 4 x 9 = 36
3 x 10 = 30 4 x 10 = 40
------------- -------------
5 x 1 = 5 6 x 1 = 6
5 x 2 = 10 6 x 2 = 12
5 x 3 = 15 6 x 3 = 18
5 x 4 = 20 6 x 4 = 24
5 x 5 = 25 6 x 5 = 30
5 x 6 = 30 6 x 6 = 36
5 x 7 = 35 6 x 7 = 42
5 x 8 = 40 6 x 8 = 48
5 x 9 = 45 6 x 9 = 54
5 x 10 = 50 6 x 10 = 60
------------- -------------
7 x 1 = 7 8 x 1 = 8
7 x 2 = 14 8 x 2 = 16
7 x 3 = 21 8 x 3 = 24
7 x 4 = 28 8 x 4 = 32
7 x 5 = 35 8 x 5 = 40
7 x 6 = 42 8 x 6 = 48
7 x 7 = 49 8 x 7 = 56
7 x 8 = 56 8 x 8 = 64
7 x 9 = 63 8 x 9 = 72
7 x 10 = 70 8 x 10 = 80
------------- -------------
9 x 1 = 9 10 x 1 = 10
9 x 2 = 18 10 x 2 = 20
9 x 3 = 27 10 x 3 = 30
9 x 4 = 36 10 x 4 = 40
9 x 5 = 45 10 x 5 = 50
9 x 6 = 54 10 x 6 = 60
9 x 7 = 63 10 x 7 = 70
9 x 8 = 72 10 x 8 = 80
9 x 9 = 81 10 x 9 = 90
9 x 10 = 90 10 x 10 = 100
------------- -------------
If you can live without the dashes, it gets even easier.