Populate a new table based on values of two colums-CodePudding

I have created a blank table (NEW_TABLE) with many columns, including COLUMN_1 and COLUMN_2. I would like to populate NEW_TABLE with every possible combination of COLUMN_1 found in another table, OLD_TABLE, and values of 0 and 1 for COLUMN_2. For an example, assume there are 2 distinct values in OLD_TABLE.COLUMN_1 (e.g.; 'red' and 'blue'). My desired result would be four rows in NEW_TABLE with

COLUMN_1 : COLUMN_2  
red      : 0  
red      : 1
blue     : 0
blue     : 1

CodePudding user response：

Use this to generate your permutations:

SELECT *
FROM (SELECT DISTINCT Column_1 FROM OLD_TABLE) a
CROSS JOIN (VALUES (0), (1)) AS b(Column_2)

And that can now be part of an INSERT statement:

INSERT INTO NEW_TABLE (COLUMN_1, COLUMN_2)
SELECT a.Column_1, b.Column_2
FROM (SELECT DISTINCT Column_1 FROM OLD_TABLE) a
CROSS JOIN (VALUES (0), (1)) AS b(Column_2)

CodePudding user response：

You are searching for a cross join.

I created a small table

create table TST_CJ
(
    ID    NUMBER,
    COLOR VARCHAR
);

I inserted 3 Values:

With a crossjoin I can get the desired output:

SELECT a.id,
       b.color
FROM tst.TST_CJ a
         cross join tst.TST_CJ b

Oh I just saw, that you don't have the values 0, 1 etc in a table. You can just cross join with a subquery then. Something like this:

SELECT b.id,
       a.color
FROM tst.TST_CJ a
         cross join (SELECT 1 as ID UNION ALL SELECT 2 as ID) b