SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with t as (
      select 'prod1' as name, '3,5,6,9' as col1, '33,44,66,7' as col2, '66,7' as col3
     ),
     cte as (
      select name, 1 as lev,
             convert(varchar(max), left(col1, charindex(',', col1 + ',') - 1)) as col1,
             convert(varchar(max), stuff(col1, 1, charindex(',', col1 + ','), '')) as col1_rest,
             convert(varchar(max), left(col2, charindex(',', col2 + ',') - 1)) as col2,
             convert(varchar(max), stuff(col2, 1, charindex(',', col2 + ','), '')) as col2_rest,
             convert(varchar(max), left(col3, charindex(',', col3 + ',') - 1)) as col3,
             convert(varchar(max), stuff(col3, 1, charindex(',', col3 + ','), '')) as col3_rest
      from t
      union all
      select name, lev + 1,
             left(col1_rest, charindex(',', col1_rest + ',') - 1) as col1,
             stuff(col1_rest, 1, charindex(',', col1_rest + ','), '') as col1_rest,
             left(col2_rest, charindex(',', col2_rest + ',') - 1) as col2,
             stuff(col2_rest, 1, charindex(',', col2_rest + ','), '') as col2_rest,
             left(col3_rest, charindex(',', col3_rest + ',') - 1) as col3,
             stuff(col3_rest, 1, charindex(',', col3_rest + ','), '') as col3_rest
      from cte
      where (col1_rest <> '' or col2_rest <> '' or col3_rest <> '') and lev < 10
     )
select name, col1, col2, col3
from cte

name	col1	col2	col3
prod1	3	33	66
prod1	5	44	7
prod1	6	66
prod1	9	7	null