How to transpose a single table row to multiple rows in MySQL -alternate version
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', ',' );
Recent Comments