MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
id INT,
subOperation VARCHAR(50));

INSERT INTO mytable VALUES
(1,'test1'),
(2,'test2'),
(3,'test3'),
(4,'test1,test2'),
(5,'test1,test3');
SET @sql := NULL;
  
SET @sql :=  (SELECT CONCAT('SELECT (@rownum := @rownum+1) AS Rownum, id_suboperation, id_element FROM (SELECT * FROM (',GROUP_CONCAT(qv SEPARATOR ' union all '),' ) sq GROUP BY id_suboperation, id_element) vr CROSS JOIN (SELECT @rownum := 0) rn ORDER BY id_suboperation, id_element;')
FROM
(SELECT CONCAT("SELECT id AS id_suboperation, 
      REVERSE(REVERSE(SUBSTRING_INDEX(
          SUBSTRING_INDEX(subOperation,',',",idx,"),',',-1))+0) AS id_element
FROM mytable") qv FROM mytable a CROSS JOIN
(SELECT 1 IDX UNION 
 SELECT 2 UNION 
 SELECT 3 UNION 
 SELECT 4) b
GROUP BY qv) c);

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


Rownum
id_suboperation
id_element


1
1
1

2
2
2

3
3
3

4
4
1

5
4
2

6
5
1

7
5
3


SELECT @sql;


@sql


SELECT (@rownum := @rownum+1) AS Rownum, id_suboperation, id_element FROM (SELECT * FROM (SELECT id AS id_suboperation, 
      REVERSE(REVERSE(SUBSTRING_INDEX(
          SUBSTRING_INDEX(subOperation,',',1),',',-1))+0) AS id_element
FROM mytable union all SELECT id AS id_suboperation, 
      REVERSE(REVERSE(SUBSTRING_INDEX(
          SUBSTRING_INDEX(subOperation,',',2),',',-1))+0) AS id_element
FROM mytable union all SELECT id AS id_suboperation, 
      REVERSE(REVERSE(SUBSTRING_INDEX(
          SUBSTRING_INDEX(subOperation,',',3),',',-1))+0) AS id_element
FROM mytable union all SELECT id AS id_suboperation, 
      REVERSE(REVERSE(SUBSTRING_INDEX(
          SUBSTRING_INDEX(subOperation,',',4),',',-1))+0) AS id_element
FROM mytable ) sq GROUP BY id_suboperation, id_element) vr CROSS JOIN (SELECT @rownum := 0) rn ORDER BY id_suboperation, id_element;


INSERT INTO mytable VALUES
(6,'tes2,testui4,testa5'),
(7,'testqa3,testas53'),
(8,'testdsd3');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Rownum
id_suboperation
id_element


1
1
1

2
2
2

3
3
3

4
4
1

5
4
2

6
5
1

7
5
3

8
6
2

9
6
4

10
6
5

11
7
3

12
7
53

13
8
3