question

Dalex avatar image
Dalex asked

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

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

joinscombinations
1 comment
10 |1200

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

TimothyAWiseman avatar image TimothyAWiseman commented ·
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 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered

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

2 comments
10 |1200

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

TimothyAWiseman avatar image TimothyAWiseman commented ·
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 ·
Peso avatar image Peso commented ·
Where is the DISTINCT keyword?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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