Mysql Pivot rows into dynamic columns with unknown number of columns

Question

I have an output of a query as follows:

bid  | code | amount        |
-----------------------------
2915 | BF   |   -10700.00   |
2915 | YQ   |   -300.00     |
2915 | YR   |   0.00        |
2915 | YM   |   0.00        |
2915 | WO   |   -153.00     |
2915 | IN   |   -329.00     |
2915 | K3   |   0.00        |
2915 | CUTE |   -50.00      |
-----------------------------

I need to convert the values in code column to individual columns with column name as corresponding code and amount shall be the value.

bid  | BF       | YQ     | ... | CUTE  |
----------------------------------------
2915 | -10700.00| -300.00| ... | -50.00|

The issue is that the result of my initial query has dynamic codes and hence need to generate columns accordingly.

Already tried the following logic but couldn't achieve the goal:

dynamic pivot table using mysql, mysql rows to columns, mysql transpose

All the above links use some expression or the other to generate the columns.

My original resulting rows are result of simple select on multiple joined tables, and has no calculation or expression scope.

Kindly suggest some pointer or hint on how to achieve this.

Many thanks in advance.

Answer 1

Procedure:

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 ;

Usage example:

/* USE test; */
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;