BrainImplants

Here’s my tweaked version of the code presented at this article:

lembra.wordpress.com/2011/10/01/how-to-transpose-a-single-table-row-to-multiple-rows-in-mysql

In the end I never used this for production and I used different tools entirely. I just wanted to archive this here in case I need it again in the future.

Changes form the original code:
The results table name is generated by combining the table name, key field name, and “_list”
Avoids creating rows with blank keys.
I dropped the use of the view in favour of a temporary table. The crux of the problem is that a cursor can’t use a variable table name. My research suggests that you must either use a fixed view name or a (temporary) fixed table.

I wonder if you can write a function to generate a result set from a variable table name and pass that result to a function to iterate over it, but time does not permit me to look into it further.

DELIMITER $$
DROP PROCEDURE IF EXISTS split_column $$
CREATE PROCEDURE split_column( table_name VARCHAR(256), key_field VARCHAR(256), value_field VARCHAR(256), delim VARCHAR(16) )
BEGIN  
  DECLARE read_key VARCHAR(256);  
  DECLARE read_value VARCHAR(256);
  DECLARE occurance int default 0;  
  DECLARE i INT DEFAULT 0;  
  DECLARE splitted_field VARCHAR(60);  
  DECLARE done int default 0;  
  DECLARE result_table_name VARCHAR(256);
  DECLARE cur CURSOR FOR SELECT * FROM temp_table;   
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  SET result_table_name = CONCAT_WS( '_', table_name, key_field, 'list');  

  SET @stm = CONCAT( 'DROP TABLE IF EXISTS ', result_table_name );  
  PREPARE stmt FROM @stm;  
  EXECUTE stmt;  
  DEALLOCATE prepare stmt;

  DROP TEMPORARY TABLE IF EXISTS temp_table;  

  SET @stm = CONCAT( 'CREATE TEMPORARY TABLE temp_table AS (SELECT ', key_field, ', ', value_field, ' FROM ', table_name, ')' );  
  PREPARE stmt FROM @stm;  
  EXECUTE stmt;  
  DEALLOCATE prepare stmt;

  SET @stm = CONCAT( 'CREATE TABLE ', result_table_name,'( ', key_field, ' VARCHAR(256), ', value_field, ' VARCHAR(256) )' );  
  PREPARE stmt FROM @stm;  
  EXECUTE stmt;  
  DEALLOCATE PREPARE stmt;
      
  OPEN cur;    
    read_loop: LOOP
      FETCH cur INTO read_key, read_value;                   
      IF done THEN        
        LEAVE read_loop;      
      END IF;        
      SET occurance = (SELECT LENGTH(read_key) - LENGTH(REPLACE(read_key, delim, '') ) + 1 );                
      SET i = 1;
      WHILE i <= occurance DO        
        SET splitted_field = REPLACE( (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(read_key, delim, i), length(SUBSTRING_index(read_key, delim, i - 1)) + 1), ',', '') ), delim, '');         
        IF LENGTH(splitted_field) > 0 THEN
			SET @stm = CONCAT( 'INSERT INTO ', result_table_name, ' VALUES ("', splitted_field, '", "', read_value, '")' );        
			PREPARE stmt FROM @stm;        
			EXECUTE stmt;        
			DEALLOCATE PREPARE stmt;                 
        END IF;
      SET i = i + 1;      
      END WHILE;       
    END LOOP;  
  CLOSE cur;   
  DROP TEMPORARY TABLE IF EXISTS temp_table;  
END; $$
DELIMITER ;

# Example calling syntax for a list of blogs with a categories field like "rants,nerds,linux,supernerds"
CALL split_column( 'some_table', 'keys_field', 'value_field', 'delimeter' );
CALL split_column( 'posts', 'categories', 'id', ',' );