By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t as
select 1 as id, 'test1' as subOperation union all
select 2 as id, 'test2' as subOperation union all
select 3 as id, 'test3' as subOperation union all
select 4 as id, 'test1,test2' as subOperation union all
select 5 as id, 'test1,test3'
select row_number() over (order by id_suboperation, id_element) as id, tt.*
from ((select id as id_suboperation, id as id_element
from t
where t.subOperation not like '%,%'
) union all
(select t.id, tsub.id
from t join
t tsub
on substring_index(t.subOperation, ',', 1) = tsub.subOperation
where t.subOperation like '%,%'
) union all
(select t.id, tsub.id
from t join
t tsub
on substring_index(t.subOperation, ',', -1) = tsub.subOperation
where t.subOperation like '%,%'
)
) tt
| id | 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 |