Retrieving all combinations from a database table with alternative values

Say we have a simple DB-table tab created in MySql with

CREATE TABLE `tab` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `val` int(11) DEFAULT NULL,
 `altval` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

and filled with an examplary set of data:

INSERT INTO tab VALUES(1,1,NULL);
INSERT INTO tab VALUES(2,2,3);
INSERT INTO tab VALUES(3,4,NULL);
INSERT INTO tab VALUES(4,5,6);

Each row has a value val and for id=2,4 an alternative value altval. Is there a way in SQL to retrieve all combinations for abitrary data sets? (The number of combinations is 2 power the number of rows with alternative datasets, here: 4, therefore ‘abitrary’ is of course limited.) For the given example, the result should be: (1,2,4,5),(1,2,4,6),(1,3,4,5),(1,3,4,6)

In a script language like php, I would solve this problem by generating nested for-loops using eval().

Answer

Using recursive CTE:

WITH RECURSIVE cte AS (
   -- continuous rn
   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 (
  -- anchor
  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
  -- recursive part
  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);  -- choosing only longest string

db<>fiddle demo