How to multiply 2 tables in SQl

user_11915442 0
Viewed 146

Preface: this isn't homework - I am learning SQL.

I was given the problem to make a multiplication chart.

Here's my solution:

create table x (x int)
create table y ([1] int, [2] int, [3] int, [4] int, [5] int, [6] int, [7] int, [8] int, [9] int, [10] int)

insert into x (x)
values ('1')
insert into x (x)
values ('2')
insert into x (x)
values ('3')
insert into x (x)
values ('4')
insert into x (x)
values ('5')
insert into x (x)
values ('6') 
insert into x (x)
values ('7') 
insert into x (x)
values ('8')
insert into x (x)
values ('9')
insert into x (x)
values ('10')

insert into y ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
values ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10')


select * from x
select * from y

select  x * y.[1] as [1's table],
        x * y.[2] as [2's table],
        x * y.[3] as [3's table],
        x * y.[4] as [4's table],
        x * y.[5] as [5's table],
        x * y.[6] as [6's table],
        x * y.[7] as [7's table],
        x * y.[8] as [8's table],
        x * y.[9] as [9's table],
        x * y.[10] as [10's table]
        from x cross join y

This all seems very messy and typing it out was a bit tedious. I wanted to try to do it without having to specify each column in the y table as so:

select x * y from x, y

But this didn't work as SQL is asking for a column. But I want it to perform multiplication across all columns. How do I make my times table more efficiently?

sql-server sql
1 Answers

Unfortunately there is no built-in "give me all the numbers from 1-n" (never mind "give me all of 1-n times 1-n") - but we can build it manually using a recursive CTE cross applied to each value in the set:

; -- see sqlblog.org/cte
WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < 10)
SELECT src = QUOTENAME(n.n), p.* FROM n CROSS APPLY 
(VALUES(n.n*1,n.n*2,n.n*3,n.n*4,n.n*5,n.n*6,n.n*7,n.n*8,n.n*9,n.n*10)) 
AS p([* 1],[* 2],[* 3],[* 4],[* 5],[* 6],[* 7],[* 8],[* 9],[* 10]);

Output:

src * 1 * 2 * 3 * 4 * 5 * 6 * 7 * 8 * 9 * 10
[1] 1 2 3 4 5 6 7 8 9 10
[2] 2 4 6 8 10 12 14 16 18 20
[3] 3 6 9 12 15 18 21 24 27 30
[4] 4 8 12 16 20 24 28 32 36 40
[5] 5 10 15 20 25 30 35 40 45 50
[6] 6 12 18 24 30 36 42 48 54 60
[7] 7 14 21 28 35 42 49 56 63 70
[8] 8 16 24 32 40 48 56 64 72 80
[9] 9 18 27 36 45 54 63 72 81 90
[10] 10 20 30 40 50 60 70 80 90 100

Arguably, that's not much better than your original attempt, because you still have to hard-code a bunch of sequential numbers, and that isn't fun.

With a little more effort you can build this so it can construct the table dynamically, for any upper bound (within reason - if you go beyond 100 you'll need to add OPTION (MAXRECURSION)), and without you hardcoding any number except defining the single upper bound value (note this part of the solution assumes SQL Server 2017 or better):

DECLARE @TimesTableUpperBound int = 15;

DECLARE @cols nvarchar(max) = N'',
        @piv  nvarchar(max) = N'',
        @sql  nvarchar(max) = N';WITH n(n) AS 
  (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < @ub)
SELECT src = QUOTENAME(n.n), p.* FROM n CROSS APPLY 
(VALUES(';

; -- see sqlblog.org/cte
WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 
  FROM n WHERE n < @TimesTableUpperBound)
SELECT @cols = STRING_AGG(CONCAT('n.n*',n),','),
       @piv  = STRING_AGG(QUOTENAME(CONCAT('* ',n)),',')       
FROM n;

SELECT @sql += @cols + N'))
AS p(' + @piv + ');';

SELECT @sql;
EXEC sys.sp_executesql @sql, N'@ub int', @TimesTableUpperBound;

I'm not going to copy the output here but you can play with any positive integer for upper bound here:

That isn't that much fun either, but you can tuck that away in a stored procedure, and any time you need a multiplication table, you don't have to remember how to build recursive CTEs or string aggregation or dynamic SQL, you just call:

EXEC dbo.BuildMyNumbersTimesTable @TimesTableUpperBound = 32;
user_613050
Related