I have two columns in tables which I can not join, but I need make all possible combinations from two columns f.e.:
Table A ColX
1 1 2 3
TableB ColY
1 3 2 1
Must give answer:
11 12 13 21 22 23 31 32 33
asked
How to make all possible combination from two columns?
I just noticed the "I can not join." The best way to do this is with a join as FatherJack did. If you are restricted from doing that (homework assignment?) then you can wite a function that will generate all values in a procedural fashion using a loop. But under normal circumstances, use the cross join.
Try this:
USE [adventureworks]
CREATE table tabOne ( Colx CHAR(1) ) ;
CREATE table tabTwo ( Coly CHAR(1) ) ;
INSERT INTO tabone
SELECT ( 1 )
UNION
SELECT ( 2 )
UNION
SELECT ( 4 ) ;
INSERT INTO tabtwo
SELECT ( 3 )
UNION
SELECT ( 7 )
UNION
SELECT ( 8 ) ;
SELECT colx + coly AS variations
FROM tabone,
tabtwo
ORDER BY variations
DROP TABLE tabone
DROP TABLE tabtwo
You may need to convert the columns to character rather than integer type in order to be able to CONCATENATE(+) them rather than SUM them ...
To summarize, this is an implicit cross join, and a cross join by definition creates all possible combinations. This is also called the cartesian product, especially when coming at it from a set theoretic standpoint.
