Created
August 31, 2014 11:42
-
-
Save chappy84/f9b5105263b10c9c6a63 to your computer and use it in GitHub Desktop.
MySQL Pivot Stored Procedure
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP PROCEDURE IF EXISTS pivot_user_preferences; | |
DELIMITER $$ | |
CREATE PROCEDURE pivot_user_preferences(IN user_id INT) | |
BEGIN | |
DECLARE done INT DEFAULT FALSE; | |
DECLARE definition_label CHAR(255); | |
DECLARE label_cursor CURSOR FOR SELECT DISTINCT label FROM example_db.user_preferences; | |
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; | |
SET @user_id = user_id; | |
SET @sql_statement = 'SELECT u.* '; | |
OPEN label_cursor; | |
read_loop: LOOP | |
FETCH label_cursor INTO definition_label; | |
IF done THEN | |
LEAVE read_loop; | |
END IF; | |
SET @sql_statement = CONCAT(@sql_statement, ', GROUP_CONCAT(IF(up.label = \'', definition_label, '\', up.value, NULL)) AS ', definition_label); | |
END LOOP; | |
CLOSE label_cursor; | |
SET @sql_statement = CONCAT(@sql_statement, ' FROM users u LEFT JOIN user_preferences up ON u.id = up.user_id WHERE u.id = (?) GROUP BY id'); | |
PREPARE stmt FROM @sql_statement; | |
EXECUTE stmt USING @user_id; | |
DEALLOCATE PREPARE stmt; | |
END $$ | |
DELIMITER ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment