// Normalize table by creating a child table with the unique field and each value of the comma separated column.


DROP TABLE IF EXISTS SplitValues;
DROP PROCEDURE IF EXISTS split_string;
DROP PROCEDURE IF EXISTS normalize_table;

CREATE TABLE `SplitValues` (
`cid` varchar(40),
`value` varchar(500)
) ENGINE=MyISAM;

DELIMITER //

CREATE PROCEDURE split_string (pcd TEXT, pinput TEXT, pdmtr VARCHAR(10))
DETERMINISTIC
BEGIN
DECLARE cur_position INT DEFAULT 1;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(1000);
DECLARE delimiter_length TINYINT UNSIGNED;

SET remainder = pinput;
SET delimiter_length = CHAR_LENGTH(pdmtr);

WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, pdmtr);
IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = LEFT(remainder, cur_position - 1);
END IF;
IF TRIM(cur_string) != '' THEN
INSERT INTO SplitValues (cid, value) VALUES (pcd, cur_string);
END IF;
SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
END WHILE;
END
//

CREATE PROCEDURE normalize_table (ptable VARCHAR(64), pkey TEXT, pvalue TEXT, pdel VARCHAR(10))
DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE vkey,vvalue TEXT;
SET @vquery = CONCAT("CREATE VIEW tmpnormview AS SELECT ", pkey,",",pvalue," FROM ",ptable);

DROP VIEW IF EXISTS tmpnormview;
PREPARE stmt FROM @vquery;
EXECUTE stmt;

BEGIN
DECLARE cur CURSOR FOR SELECT * FROM tmpnormview;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur INTO vkey,vvalue;
IF NOT done THEN
CALL split_string(vkey,vvalue,pdel);
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END;
DROP VIEW tmpnormview;
DEALLOCATE PREPARE stmt;
END
//

DELIMITER ;


Read more: http://feeds.dzone.com/~r/dzone/snippets/~3/6V6bJL8QWm0/13779