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!

Advertisements

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

I need to use a multi-valued field in a table some days before, where I need to associate a list of tags to webpages, similar to the bookmark manager del.icio.us. The adopted modeling alternative was create a relational table with two fields (webpage-key and tag-key) and map the tags to the correspondent webpage. So, the tags are in a column and one webpage may have more than one tag. How to transpose those tags, to show each webpage and it list of tags in a single line? In MySQL you can use ‘GROUP_CONCAT’:

DROP TABLE IF EXISTS page, tag, page_tag;

CREATE TABLE page(pid INT PRIMARY KEY, url VARCHAR(32) );

CREATE TABLE tag(tid INT PRIMARY KEY, tag VARCHAR(32) );

CREATE TABLE page_tag(pid int, tid varchar(1), PRIMARY KEY pk(pid, tid) );

INSERT INTO page VALUES 
   (1, 'https://lembra.wordpress.com'), 
   (2, 'http://grooveshark.com'), 
   (3, 'http://stackoverflow.com');

INSERT INTO tag VALUES 
   (1, 'blog'), (2, 'music'), (3, 'questions'), (4, 'social-network'), (5, 'rss');

INSERT INTO page_tag VALUES 
   (1, 1), (2, 2), (3, 3), (1, 4), (2, 4), (3, 4), (1, 5);

SELECT url, GROUP_CONCAT(tag) tags 
FROM page p, page_tag r, tag t 
WHERE p.pid = r.pid AND r.tid = t.tid 
GROUP BY url;

-- +-----------------------------+--------------------------+
-- | 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)

See ya!