This is achieved by using cross joins on the same table (eg: Cartesian product)
An example:
Table1 contains the following values in one single column:
0
1
2
3
Cross joining table1 and adding decimal weights with itself reveals:
00
01
02
03
10
11
12
13
21
22
23
31
32
33
See the concrete example:
CREATE TABLE @integers (i INTEGER NOT NULL PRIMARY KEY);
INSERT INTO @integers VALUES (0);
INSERT INTO @integers VALUES (1);
INSERT INTO @integers VALUES (2);
INSERT INTO @integers VALUES (3);
INSERT INTO @integers VALUES (4);
INSERT INTO @integers VALUES (5);
INSERT INTO @integers VALUES (6);
INSERT INTO @integers VALUES (7);
INSERT INTO @integers VALUES (8);
INSERT INTO @integers VALUES (9);
GO
SELECT 1000*m.i+100*c.i+10*x.i+u.i
FROM @integers m, @integers c, @integers x, @integers u
DROP TABLE @integers
GO
Another way, if the highest number to be generate is less than 100, is to use a recursive CTE:
WITH X
AS
(
SELECT 1 as number
UNION ALL
SELECT number+1
FROM X
WHERE number+1 < 100
)
SELECT * FROM X