MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable
(id1 VARCHAR(20),
 id2 INT);
INSERT INTO mytable VALUES
('A',1),
('A',2),
('B',3),
('C',4)
SET @cols = NULL;
SET @tables = NULL;
SET @conditions = NULL;
SET @sql = NULL;

SET @cols := (WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT GROUP_CONCAT(CONCAT('c',idx,'.id2')) FROM wo);

SELECT @cols;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

SET @tables := (WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT GROUP_CONCAT(CONCAT('combinationTable c',idx) SEPARATOR ' CROSS JOIN ') FROM wo);

SELECT @tables;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

SET @conditions := (SELECT GROUP_CONCAT(conds SEPARATOR ' AND ')
FROM
(WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT CONCAT('c',idx,'.rn < c',LAG(idx) OVER (ORDER BY idx DESC),'.rn') conds
FROM wo 
ORDER BY idx) A
WHERE conds IS NOT NULL);

SELECT @conditions;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

SET @sql := (
CONCAT('WITH combinationTable AS (
 SELECT DENSE_RANK() OVER (ORDER BY id1) AS rn ,id2, id1 FROM mytable
)
SELECT ',@cols,'
FROM ',@tables,'
WHERE ',@conditions,';'));

SELECT @sql;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


@cols


c1.id2,c2.id2,c3.id2




@tables


combinationTable c1 CROSS JOIN combinationTable c2 CROSS JOIN combinationTable c3




@conditions


c1.rn < c2.rn AND c2.rn < c3.rn




@sql


WITH combinationTable AS (
 SELECT DENSE_RANK() OVER (ORDER BY id1) AS rn ,id2, id1 FROM mytable
)
SELECT c1.id2,c2.id2,c3.id2
FROM combinationTable c1 CROSS JOIN combinationTable c2 CROSS JOIN combinationTable c3
WHERE c1.rn < c2.rn AND c2.rn < c3.rn;




id2
id2
id2


1
3
4

2
3
4


/*ADDING NEW DATA*/
INSERT INTO mytable VALUES
('C',5),
('D',6)
/*SAME PREPARED STATEMENT QUERY AS ABOVE*/
SET @cols = NULL;
SET @tables = NULL;
SET @conditions = NULL;
SET @sql = NULL;

SET @cols := (WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT GROUP_CONCAT(CONCAT('c',idx,'.id2')) FROM wo);

SELECT @cols;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

SET @tables := (WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT GROUP_CONCAT(CONCAT('combinationTable c',idx) SEPARATOR ' CROSS JOIN ') FROM wo);

SELECT @tables;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

SET @conditions := (SELECT GROUP_CONCAT(conds SEPARATOR ' AND ')
FROM
(WITH RECURSIVE wo AS (
SELECT 1 idx, COUNT(DISTINCT id1) mxct FROM mytable 
UNION ALL
SELECT idx+1, mxct FROM wo WHERE idx+1 <= mxct)
SELECT CONCAT('c',idx,'.rn < c',LAG(idx) OVER (ORDER BY idx DESC),'.rn') conds
FROM wo 
ORDER BY idx) A
WHERE conds IS NOT NULL);

SELECT @conditions;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

SET @sql := (
CONCAT('WITH combinationTable AS (
 SELECT DENSE_RANK() OVER (ORDER BY id1) AS rn ,id2, id1 FROM mytable
)
SELECT ',@cols,'
FROM ',@tables,'
WHERE ',@conditions,';'));

SELECT @sql;/*TO SEE VARIABLE VALUE ONLY, NOT NEEDED IN FINAL QUERY*/

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



@cols


c1.id2,c2.id2,c3.id2,c4.id2




@tables


combinationTable c1 CROSS JOIN combinationTable c2 CROSS JOIN combinationTable c3 CROSS JOIN combinationTable c4




@conditions


c1.rn < c2.rn AND c2.rn < c3.rn AND c3.rn < c4.rn




@sql


WITH combinationTable AS (
 SELECT DENSE_RANK() OVER (ORDER BY id1) AS rn ,id2, id1 FROM mytable
)
SELECT c1.id2,c2.id2,c3.id2,c4.id2
FROM combinationTable c1 CROSS JOIN combinationTable c2 CROSS JOIN combinationTable c3 CROSS JOIN combinationTable c4
WHERE c1.rn < c2.rn AND c2.rn < c3.rn AND c3.rn < c4.rn;




id2
id2
id2
id2


1
3
5
6

1
3
4
6

2
3
5
6

2
3
4
6