How to use cartesian join to get multiple records

Hello !
How to use correctly catesian join to get follwing result
Source table
Col1, Col2, KOKKU
1 1 4
1 2 2
1 3 1
And result must be (the column KOKKU defines how much every row must by
multiplied)
Col1, Col2
1 1
1 1
1 1
1 1
1 2
1 2
1 3
There must be come cartesian(CROSS) join solution
Kuido
Message posted via http://www.webservertalk.comI would use a Numbers table for this:
SELECT col1, col2
FROM YourTable AS T
INNER JOIN Numbers AS N
ON N.num BETWEEN 1 AND T.kokku
(BTW an INNER JOIN is a subset of a Cartesian Product)
There are plenty of ways to build a numbers table, but since you only ever
have to do it once the method isn't very important. I actually prefer to use
a loop:
CREATE TABLE Numbers
(num INTEGER PRIMARY KEY)
INSERT INTO Numbers VALUES (1)
WHILE (SELECT MAX(num) FROM Numbers)<65536
INSERT INTO Numbers (num)
SELECT num+(SELECT MAX(num) FROM Numbers)
FROM Numbers
Some other suggestions here:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
David Portas
SQL Server MVP
--