By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` int(11) DEFAULT NULL,
`altval` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO tab(id, val, altval) VALUES(1,1, NULL);
INSERT INTO tab(id, val, altval) VALUES(2,2,3);
INSERT INTO tab(id, val, altval) VALUES(3,4,NULL);
INSERT INTO tab(id, val, altval) VALUES(4,5,6);
SELECT * FROM tab;
| id | val | altval |
|---|---|---|
| 1 | 1 | null |
| 2 | 2 | 3 |
| 3 | 4 | null |
| 4 | 5 | 6 |
WITH RECURSIVE cte AS (
SELECT CAST(val AS CHAR(11)) val, CAST(altval AS CHAR(11)) AS altval, ROW_NUMBER() OVER(ORDER BY id) AS rn
FROM tab
), rec AS (
SELECT val AS result, rn
FROM cte
WHERE rn = 1
UNION ALL
SELECT altval AS result, rn
FROM cte
WHERE rn = 1
AND altval IS NOT NULL
UNION ALL
SELECT CONCAT(rec.result, ',', cte.val), cte.rn
FROM rec
JOIN cte ON cte.rn = rec.rn+1
UNION ALL
SELECT CONCAT(rec.result, ',', cte.altval), cte.rn
FROM rec
JOIN cte ON cte.rn = rec.rn+1
WHERE cte.altval IS NOT NULL
)
SELECT result
FROM rec
WHERE rn = (SELECT COUNT(*) FROM cte)
| result |
|---|
| 1,2,4,5 |
| 1,2,4,6 |
| 1,3,4,5 |
| 1,3,4,6 |
UPDATE tab SET altval = 9 WHERE id = 1;
SELECT * FROM tab;
| id | val | altval |
|---|---|---|
| 1 | 1 | 9 |
| 2 | 2 | 3 |
| 3 | 4 | null |
| 4 | 5 | 6 |
WITH RECURSIVE cte AS (
SELECT CAST(val AS CHAR(11)) val, CAST(altval AS CHAR(11)) AS altval, ROW_NUMBER() OVER(ORDER BY id) AS rn
FROM tab
), rec AS (
SELECT val AS result, rn
FROM cte
WHERE rn = 1
UNION ALL
SELECT altval AS result, rn
FROM cte
WHERE rn = 1
AND altval IS NOT NULL
UNION ALL
SELECT CONCAT(rec.result, ',', cte.val), cte.rn
FROM rec
JOIN cte ON cte.rn = rec.rn+1
UNION ALL
SELECT CONCAT(rec.result, ',', cte.altval), cte.rn
FROM rec
JOIN cte ON cte.rn = rec.rn+1
WHERE cte.altval IS NOT NULL
)
SELECT result
FROM rec
WHERE rn = (SELECT COUNT(*) FROM cte)
| result |
|---|
| 1,2,4,5 |
| 9,2,4,5 |
| 9,2,4,6 |
| 1,2,4,6 |
| 9,3,4,5 |
| 1,3,4,5 |
| 1,3,4,6 |
| 9,3,4,6 |