CODEWITHSUNDEEP
sqlsql-server
jimbo1022
jimbo1022

Reputation: 39

how to do permutations combined with permutations in sql

I have created a table that does all the 3 letter combinations of the alphabet. See below

ID  1stLetter  2ndLetter  3rdLetter
1   A          B          C
2   A          B          D
3   A          B          E
and so on

I have a second table as follows:

Letter  Number
A       0
A       1
B       0
B       1
C       0
D       0
D       1
E       0
E       1
E       2

I have been trying to figure out how to combine the two so that for each row of the first table it is combined with the second table to get all possible combinations. For example, the output should look like:

NEWID  ID  1stLetter  2ndLetter  3rdLetter  1stNumber  2ndNumber  3rd Number
1      1   A          B          C          0          0          0
2      1   A          B          C          1          0          0
3      1   A          B          C          0          1          0
4      1   A          B          C          1          1          0
5      2   A          B          D          0          0          0
6      2   A          B          D          0          0          1
7      2   A          B          D          0          1          0
8      2   A          B          D          0          0          1
9      2   A          B          D          1          0          0
10     2   A          B          D          1          0          1
11     2   A          B          D          1          1          0
12     2   A          B          D          1          0          1
13     2   A          B          D          1          1          1
14     3   A          B          E     and so on

Is this possible? Any help will be greatly appreciated.

Thanks,

J

Upvotes: 0

Views: 66

Answers (2)

Juozas
Juozas

Reputation: 933

Jus use CROSS JOIN

SELECT * FROM [data source1] CROSS JOIN [data source2]

Upvotes: -1

Tanner
Tanner

Reputation: 22753

This should do the trick, hopefully the code is self explanatory, please ask if not:

CREATE TABLE #combo
    (
      id INT ,
      letter1 NVARCHAR(1) ,
      letter2 NVARCHAR(1) ,
      letter3 NVARCHAR(1)
    );

CREATE TABLE #numbers
    (
      letter NVARCHAR(1) ,
      number INT
    );

INSERT  INTO #combo
        ( id, letter1, letter2, letter3 )
VALUES  ( 1, 'A', 'B', 'C' ),
        ( 2, 'A', 'B', 'D' ),
        ( 3, 'A', 'B', 'E' );

INSERT  INTO #numbers
        ( letter, number )
VALUES  ( 'A', 0 ),
        ( 'A', 1 ),
        ( 'B', 0 ),
        ( 'B', 1 ),
        ( 'C', 0 ),
        ( 'C', 1 ),
        ( 'D', 0 ),
        ( 'D', 1 ),
        ( 'E', 0 ),
        ( 'E', 1 ),
        ( 'E', 2 );

SELECT  c.* ,
        n1.number ,
        n2.number ,
        n3.number
FROM    #combo c
        INNER JOIN #numbers n1 ON c.letter1 = n1.letter
        INNER JOIN #numbers n2 ON c.letter2 = n2.letter
        INNER JOIN #numbers n3 ON c.letter3 = n3.letter
ORDER BY id;

DROP TABLE #combo;
DROP TABLE #numbers;

Upvotes: 2

Related Questions