DELIMITER @@;

DROP PROCEDURE IF EXISTS pivot@@;
CREATE PROCEDURE pivot ( IN schema_name VARCHAR(64) /* database name */
                       , IN table_name VARCHAR(64)  /* table name */
                       , IN id_name VARCHAR(64)     /* row values field name */
                       , IN key_name VARCHAR(64)    /* col values field name, 
                                                           must be char or varchar type 
                                                           and <= 64 chars long */
                       , IN value_name VARCHAR(64)  /* val values field name */
                       )
pivot:BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @error := 1;
    SET @error := 0;
    SELECT character_maximum_length 
        INTO @maxlen 
        FROM information_schema.columns
        WHERE table_schema = schema_name
          AND table_name = table_name
          AND column_name = key_name
          AND data_type IN ('char', 'varchar');
    IF @error OR !@maxlen OR @maxlen IS NULL THEN
        SELECT '@error OR @maxlen=0 OR @maxlen IS NULL', @error, @maxlen;
        LEAVE pivot;
    END IF;
    DROP TEMPORARY TABLE IF EXISTS temp_pivot;
    SET @sql := CONCAT('CREATE TEMPORARY TABLE temp_pivot (key_name VARCHAR(',
                       @maxlen,
                       ')) ENGINE=Memory SELECT DISTINCT `',
                       key_name,
                       '` key_name FROM `',
                       schema_name,
                       '`.`',
                       table_name,
                       '`;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    SELECT GROUP_CONCAT(CONCAT( ', MAX(CASE `',
                                key_name,
                                '` WHEN ''',
                                temp_pivot.key_name,
                                ''' THEN `',
                                value_name,
                                '` END) `',
                                temp_pivot.key_name,
                                '`') SEPARATOR '')
        INTO @sql
        FROM temp_pivot;
    DROP TEMPORARY TABLE temp_pivot;
    SET @sql := CONCAT('SELECT `',
                       id_name,
                       '`',
                       @sql,
                       ' FROM `',
                       schema_name,
                       '`.`',
                       table_name,
                       '` GROUP BY `',
					   id_name,
					   '`;');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DROP PREPARE stmt;
    SET @error := NULL;
    SET @maxlen := NULL;
    SET @sql := NULL;
    
END pivot@@;

DELIMITER ;

DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab(id INT, `key` VARCHAR(16), val INT);
INSERT INTO testtab (id, `key`, val)
VALUES (1,'key1',11),
       (1,'key2',12),
       (1,'key3',13),
       (2,'key1',21),
       (2,'key2',22),
       (2,'key4',24),
       (3,'key1',31),
       (3,'key2',32),
       (3,'key3',33),
       (3,'key4',34);
SELECT * FROM testtab;
       
CALL pivot('test', 'testtab', 'id', 'key', 'val');

DROP PROCEDURE pivot;
DROP TABLE testtab;