Recently whilst writing some code I needed to pivot rows to columns in a MySQL results set. Now with some other DB providers this is standard functionality. Oracle (of which I have previous experience of implementing pivot with), PostgreSQL and MS SQLServer all provide this. Unfortunately MySQL in it’s ultimate wisdom has never implemented this feature. This lead to one of two possibilities:

1. Implement two separate queries and combine the results together in a PHP foreach loop
2. As it supports them, a MySQL Stored Procedure

The first option was obviously not preferable, it’s far slower than getting the database to do the work. This lead to the second being my obvious preferred option. My main experience of writing stored procedures and functions in SQL is using PL/SQL with an Oracle DB. I have in general used MySQL on much lighter systems, however have in the odd situation required a stored procedure, so thought why not. The solution was to use a cursor in the stored procedure looping through said cursor to generate a SQL statement, then executing that as a prepared statement to return the results set required. This is then called from PHP by using the MySQL “CALL” functionality.

Here’s an example of the stored procedure I produced:

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 ;