How to generate multiple combinations from tables using SQL?-CodePudding

I have few combinations of records in my table but i want to generate Multiple combinations from original table.

Records in table

Expected Output

CodePudding user response：

If you are doing it manually you can just use an insert statement for each row you want to add, eg:

INSERT INTO table (codeset, codevalue, column, system ) VALUES ('test3', 3, 'col2', 'sys1' );

CodePudding user response：

You want to self join all test3 rows. One way:

select codeset, codevalue, column, system
from mytable
where codeset <> 'test3'
union all
select t1.codeset, t1.codevalue, t2.column, t2.system
from (select * from mytable where codeset = 'test3') t1
cross join (select * from mytable where codeset = 'test3') t2
order by codeset, column;

(It is not clear from your explanation and sample data whether you want to show the system belonging to the codeset/codevalue or to the column. I took it from the column row. If you want it elsewise, change the qualifier from t2 to t1.)

Here is another, shorter approach, that you may or may nt like better:

select t1.codeset, t1.codevalue, t2.column, t2.system
from mytable t1
join mytable t2 on t2.rowid = t1.rowid or (t2.codeset = 'test3' and t1.codeset = 'test3')
order by t1.codeset, t2.column;