CREATE PROCEDURE pivot(
    IN database_name VARCHAR(32),
    IN base_table_name VARCHAR(32),
    IN base_primary_key_name VARCHAR(32),
    IN pivoted_primary_key_name VARCHAR(32)
)
BEGIN
    DECLARE base_col_name VARCHAR(32);
    DECLARE pivoted_table_name VARCHAR(32);
    DECLARE no_more_columns BOOL DEFAULT FALSE;
    
    -- Cursor to iterate over the base table column names.
    DECLARE base_col_cursor CURSOR FOR
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = database_name
          AND table_name   = base_table_name
          AND ordinal_position > 1
        ORDER BY ordinal_position;
          
    -- Handler for going past the last base table column name.
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET no_more_columns = TRUE;
    
    -- Count of rows in the base table.
    CALL exec_stmt(CONCAT(
        'SELECT COUNT(*) INTO @row_count ',
        'FROM ', base_table_name
    ));
    
    -- Count of columns in the base table.
    SELECT MAX(ordinal_position) - 1 INTO @col_count
    FROM information_schema.columns
    WHERE table_schema = database_name
      AND table_name   = base_table_name;
    
    SET pivoted_table_name = CONCAT(base_table_name, '_pivoted');
    CALL exec_stmt(CONCAT(
        'DROP TABLE IF EXISTS ', pivoted_table_name
    ));
    
    -- Create a comma-separated list of pivoted table column names.
    CALL exec_stmt(CONCAT(
        'SELECT GROUP_CONCAT(', base_primary_key_name, ') ',
        ' INTO @pivoted_col_names FROM ', base_table_name
    ));
    
    SET @pivoted_col_names = CONCAT(pivoted_primary_key_name, 
                                    ', ', @pivoted_col_names);

    -- Use the base table's primary key values to create
    -- a comma-separated list of the field definitions
    -- to be used to create the pivoted table.
    SET @key = base_primary_key_name;
    CALL exec_stmt(CONCAT(
        "SELECT GROUP_CONCAT(", @key, ", ' INT') ",
        "INTO @field_list ",
        "FROM ", base_table_name
    ));

    -- Create the pivoted table.    
    CALL exec_stmt(CONCAT(
        'CREATE TABLE ', pivoted_table_name,
        '(', pivoted_primary_key_name, ' VARCHAR(32) NOT NULL, ', 
        @field_list,
        ', PRIMARY KEY (', pivoted_primary_key_name, ') ',
        ')'
    ));
    
    OPEN base_col_cursor;

    -- Fetch the first column name of the base table.
    FETCH base_col_cursor INTO base_col_name;
    
    -- Loop over the columns of the base table.
    WHILE NOT no_more_columns DO
        -- Create a comma-separated list of column values
        -- from the base table.
        CALL exec_stmt(CONCAT(
            'SELECT GROUP_CONCAT(', base_col_name, ') ',
            'INTO @pivoted_values ',
            'FROM ', base_table_name
        ));
        
        SET @pivoted_values = CONCAT("'", base_col_name, 
                                     "', ", @pivoted_values);
        
        -- Insert the column values as a row
        -- into the pivot table.
        CALL exec_stmt(CONCAT(
            'INSERT INTO ', pivoted_table_name,
            '(', @pivoted_col_names, ') ',
            'VALUES (', @pivoted_values, ')'
        ));
        
        COMMIT;
        
        -- Fetch the next column name of the base table.
        FETCH base_col_cursor INTO base_col_name;
    END WHILE;   
    
    CLOSE base_col_cursor;
END;
