How to transpose a single table row to multiple rows in MySQL

Some weeks ago I posted how to transpose in MySQL multiple table rows of a
given column to a single table row (separated by a DELIMITER) following a given
group criteria using ‘group_concat’. I also need some times the opposite, to break
one field with several data separated by a DELIMITER (ex: “blog,social-network,rss”)
in several rows. Consider the same example of the previous post, where we have
a list of tags in the format as follows:

-- +-----------------------------+--------------------------+
-- | url                         | tags                     |
-- +-----------------------------+--------------------------+
-- | http://grooveshark.com      | music,social-network     |
-- | https://lembra.wordpress.com | blog,social-network,rss  |
-- | http://stackoverflow.com    | questions,social-network |
-- +-----------------------------+--------------------------+
-- 3 rows in SET (0.00 sec)

And we want to pass it to the following format:

-- +-----------------------------+----------------+
-- | url                         | tag            |
-- +-----------------------------+----------------+
-- | http://grooveshark.com      | music          |
-- | http://grooveshark.com      | social-network |
-- | https://lembra.wordpress.com | blog           |
-- | https://lembra.wordpress.com | social-network |
-- | https://lembra.wordpress.com | rss            |
-- | http://stackoverflow.com    | questions      |
-- | http://stackoverflow.com    | social-network |
-- +-----------------------------+----------------+
-- 7 rows in SET (0.00 sec)

I do not known any feature of MySQL that provides that behavior directly, inspired
by Marco Gonçalves’s blog, I implement a generic procedure called ‘split_column’,
which receive the name of the table, the field that you want to break (ex: ‘tags’)
and the DELIMITER (ex: ‘,’). Meanwhile in the Marco’s procedure it uses a static
table assignment, which requires to be changed (changing table name and column name)
each time you need to use.

By the way there is a constraint in MySQL that avoid to use dynamic SQL (a query
built by an string dinamicaly) for cursor declaration (this is performed in compilation
time). Meanwhile it is possible to create a dynamic SQL to a table view. So I
create a procedure that creates a view (a logical table representation, it is not
materialized in physical disk) to the target field of the input table and use
this view statically in the Marco’s procedure. The output is stored in a table
with name given by the break column name followd by a ‘list’ suffix (ex: ‘tags_list’).

-- Creates this only once -- 

DELIMITER $$
DROP PROCEDURE IF EXISTS create_view $$
CREATE PROCEDURE create_view( table_name VARCHAR(256), field_name VARCHAR(256), prefix VARCHAR(256) ) 
BEGIN   
  DROP VIEW IF EXISTS table_view;    
  SET @stm = CONCAT( 'CREATE VIEW table_view AS SELECT ', field_name, ', ', prefix, ' FROM ', table_name );   
  PREPARE stmt FROM @stm;   
  EXECUTE stmt;   
  DEALLOCATE prepare stmt; 
END; $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS split_table $$
CREATE PROCEDURE split_table( field_name VARCHAR(256), prefix VARCHAR(256), delim VARCHAR(16) ) 
BEGIN   
  DECLARE read_field VARCHAR(256);   
  DECLARE read_prefix VARCHAR(256);
  DECLARE occurance int default 0;   
  DECLARE i INT DEFAULT 0;   
  DECLARE splitted_field VARCHAR(60);   
  DECLARE done int default 0;   
  DECLARE cur CURSOR FOR SELECT * FROM table_view;    
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;    
  SET @stm = CONCAT( 'DROP TABLE IF EXISTS ', field_name, '_list' );   
  PREPARE stmt FROM @stm;   
  EXECUTE stmt;   
  DEALLOCATE PREPARE stmt;    
  SET @stm = CONCAT( 'CREATE TABLE ', field_name, '_list( ', prefix, ' VARCHAR(256), ', field_name, ' VARCHAR(256) )' );   
  PREPARE stmt FROM @stm;   
  EXECUTE stmt;   
  DEALLOCATE PREPARE stmt;     
  OPEN cur;     
    read_loop: LOOP
      FETCH cur INTO read_prefix, read_field;                    
      IF done THEN         
        LEAVE read_loop;       
      END IF;         
      SET occurance = (SELECT LENGTH(read_field) - LENGTH(REPLACE(read_field, delim, '') ) + 1 );                 
      SET i = 1;
      WHILE i <= occurance DO         
        SET splitted_field = REPLACE( (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(read_field, delim, i), length(SUBSTRING_index(read_field, delim, i - 1)) + 1), ',', '') ), delim, '');          
        SET @stm = CONCAT( 'INSERT INTO ', field_name, '_list VALUES ("', read_prefix, '", "', splitted_field, '")' );         
        PREPARE stmt FROM @stm;         
        EXECUTE stmt;         
        DEALLOCATE PREPARE stmt;                  
        SET i = i + 1;       
      END WHILE;        
    END LOOP;   
  CLOSE cur;    
END; $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS split_column $$
CREATE PROCEDURE split_column( table_name VARCHAR(256), field_name VARCHAR(256), prefix VARCHAR(256), delim VARCHAR(16) ) 
BEGIN   
  CALL create_view( table_name, prefix, field_name );   
  CALL split_table( field_name, prefix, delim );   
  DROP VIEW IF EXISTS table_view;
END; $$
DELIMITER ;

-- Testing the split procedure -- 

DROP TABLE IF EXISTS page_tags;
CREATE TABLE page_tags(url varchar(32), tags varchar(64) );
INSERT INTO page_tags VALUES 
  ('http://grooveshark.com', 'music,social-network'),
  ('https://lembra.wordpress.com', 'blog,social-network,rss'),
  ('http://stackoverflow.com', 'questions,social-network');

SELECT * FROM page_tags;

CALL split_column( 'page_tags', 'tags', 'url', ',' );

SELECT * FROM tags_list;

See ya!