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.

• Rick James: Here's a generic pivot generator: mysql.rjweb.org/doc.php/pivot

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;

• Matthias Munz: I use er Mysql 14.14 Distrib 5.7.25 and the pivot function call returns this table with two rows: error OR maxlen=0 OR maxlen IS NULL | error | maxlen error OR maxlen=0 OR maxlen IS NULL | 1 | Do you have a clue why this happens?