SQL split values to multiple rows

Question

I have table :

id | name     1  | a,b,c     2  | b 

i want output like this :

id | name     1  | a     1  | b     1  | c     2  | b 

Answer 1

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select   tablename.id,   SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name from   numbers inner join tablename   on CHAR_LENGTH(tablename.name)      -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1 order by   id, n 

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select   tablename.id,   SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name from   (select 1 n union all    select 2 union all select 3 union all    select 4 union all select 5) numbers INNER JOIN tablename   on CHAR_LENGTH(tablename.name)      -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1 order by   id, n 

an example fiddle is here.