# question

## How to make all possible combination from two columns?

I have two columns in tables which I can not join, but I need make all possible combinations from two columns f.e.:

1 1 2 3

## TableB ColY

1 3 2 1

11 12 13 21 22 23 31 32 33

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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.
0 Likes 0 ·

·

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 ...

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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.
0 Likes 0 ·
·
Where is the DISTINCT keyword?
0 Likes 0 ·