how to convert comma separated string to single column?

Question

Table:

id    brand    group
1     adidas    1,2
2     puma      1
3     asianone  1,2,3

what result i want:

id    brand    group
1     adidias  1
2     adidias  2
3     puma     1
4     asianone 1
5     asianone 2
6     asianone 3

where id is autoincrement..

Answer 1

SELECT @id:=@id+1 AS id,
       `brand`,
       `group` 
FROM
(SELECT
  `id`, 
  `brand`,
   SUBSTRING_INDEX(SUBSTRING_INDEX(`group`, ',', n.digit+1), ',', -1) AS `group`
FROM
  Table1
  INNER JOIN
  (SELECT 0 AS digit 
   UNION ALL SELECT 1 
   UNION ALL SELECT 2 
   UNION ALL SELECT 3  
   UNION ALL SELECT 4 
   UNION ALL SELECT 5 
   UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(`group`, ',' , '')) <= LENGTH(`group`)-n.digit
  Order by ID,`group`) AS T,(SELECT @id:=0) AS R;

Output

id  brand   group
1   adidas      1
2   adidas      2
3   puma        1
4   asianone    1
5   asianone    2
6   asianone    3

Demo

http://sqlfiddle.com/#!9/be29c2/31

For Explanation goto this link and check my answer

MySql : Convert Column data to row

Answer 2

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

replace your tablename with brandstable.

Answer 3

Please use PHP explode() function.It can breaks a string into an array.

Syntax: 'explode(separator,string,limit);'