What is the opposite of GROUP_CONCAT in MySQL?

Question

I seem to come against this problem a lot, where I have data that's formatted like this:

+----+----------------------+ | id | colors               | +----+----------------------+ | 1  | Red,Green,Blue       | | 2  | Orangered,Periwinkle | +----+----------------------+ 

but I want it formatted like this:

+----+------------+ | id | colors     | +----+------------+ | 1  | Red        | | 1  | Green      | | 1  | Blue       | | 2  | Orangered  | | 2  | Periwinkle | +----+------------+ 

Is there a good way to do this? What is this kind of operation even called?

Answer 1

You could use a query like this:

SELECT   id,   SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color FROM   colors   INNER JOIN   (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n   ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit ORDER BY   id,   n.digit 

Please see fiddle here. Please notice that this query will support up to 4 colors for every row, you should update your subquery to return more than 4 numbers (or you should use a table that contains 10 or 100 numbers).

Answer 2

I think it is what you need (stored procedure) : Mysql split column string into rows

DELIMITER $$  DROP PROCEDURE IF EXISTS explode_table $$ CREATE PROCEDURE explode_table(bound VARCHAR(255))  BEGIN  DECLARE id INT DEFAULT 0; DECLARE value TEXT; DECLARE occurance INT DEFAULT 0; DECLARE i INT DEFAULT 0; DECLARE splitted_value INT; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value                                      FROM table1                                      WHERE table1.value != ''; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  DROP TEMPORARY TABLE IF EXISTS table2; CREATE TEMPORARY TABLE table2( `id` INT NOT NULL, `value` VARCHAR(255) NOT NULL ) ENGINE=Memory;  OPEN cur1;   read_loop: LOOP     FETCH cur1 INTO id, value;     IF done THEN       LEAVE read_loop;     END IF;      SET occurance = (SELECT LENGTH(value)                              - LENGTH(REPLACE(value, bound, ''))                              +1);     SET i=1;     WHILE i <= occurance DO       SET splitted_value =       (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),       LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));        INSERT INTO table2 VALUES (id, splitted_value);       SET i = i + 1;      END WHILE;   END LOOP;    SELECT * FROM table2;  CLOSE cur1;  END; $$