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 (
| @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 := (
| @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 |