I have a Mysql table That looks like this:
Col 1. Col 2.
a 1
b 2
c,d,e 3
What I would like to do is run a query that would replace the row with c,d,e with multiple broken out rows so that the result for Select * From table would look like:
Col 1. Col 2.
a 1
b 2
c 3
d 3
e 3
If you know the maximum number, you can use union all:
select substring_index(col1, ',', 1) as col1, col2
from t
union all
select substring(substring_index(col1, ',', 2), ',', -1) as col1, col2
from t
where col1 like '%,%'
union all
select substring(substring_index(col1, ',', 3), ',', -1) as col1, col2
from t
where col1 like '%,%,%';
Use a tally table. For example, mine is generated on the fly by recursive CTE
with recursive nmbs as (
select 1 n
union all
select n+1
from nmbs
where n<100
),
testdata as (
select 'a' c1, 1 c2 union all
select 'b' c1, 2 c2 union all
select 'c, ddd , e ,fgh' c1, 3
)
select c1, c2, trim(replace(substring(substring_index(c1, ',', n), length(substring_index(c1, ',', n-1))+1), ',', '')) s
from testdata
join nmbs on n <= 1 + length(c1) - length(replace(c1,',',''))
order by c1,n
