Triangular Multiplication Matrix generation

Question

Trying to generate a Triangular Multiplication Matrix IN T-SQL- Like a triangular multiplication matrix will look like this:

0
0 1
0 2 4
0 3 6 9
0 4 8 12 16

I have not been able to find an efficient solution for this. Any help is appreciated.

Answer 1

There's a clever way to do this with XML (SQL 2005 and later):

with Nums(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7 -- resize as needed or make it permanent
)
  select x as Prod from Nums
  cross apply (
    select Cast(Nums.n*(N2.n) as varchar(80))+space(3-Len(Nums.n*N2.n))
    -- expand the varchar size if needed
    as [text()]
    from Nums as N2
    where Nums.n >= n 
    order by N2.n
    for xml path('')
  ) as X(x)
  where n <= 4 -- Adjust as needed
  order by n;

(A permanent Nums table is a good idea.)

The output is this:

Prod
--------
0  
0  1  
0  2  4  
0  3  6  9  
0  4  8  12 16