SQLTeam.com | Weblogs | Forums

Create a times table of sorts


#1

Given to columns, col a = 2,4,6,8,10 and col b 2,4,6,8,10 can i create a times table matrix.
I've tried cross join which gives a list of the results. Im now googling pivots to see if thats the way to go.
The output im looking for is:
2, 4 , 6, 8, 10
2 4 8 12 16 20
4 8 16 24
6 12
8 etc,
10

I can see how to generate a table given a value, but i specifically want to use 2 columns of given values to
generate the matrix. Thankyou


#2

will this do? As always post your sample data as follows. otherwise people will skip your question because they dont want to spend the hard work putting this together for you.

create table #robcpettit(a int, b int)

insert into #robcpettit(a, b)
select 2, 2 union
select 4, 4 union
select 6, 6 union
select 8, 8 union
select 10, 10

select cast(a.a as varchar(50)) + ' x ' + cast(b.b as varchar(50)) as descr, a.a * b.b
from #robcpettit a
cross apply #robcpettit b

drop table #robcpettit