Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Pivot Stored Procedure
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
You can’t perform that action at this time.