-
-
Save ethanliew/815a3bd8823d623dfd2efbda712077bd to your computer and use it in GitHub Desktop.
MySQL Pivot Stored Procedure
This file contains 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