这很棘手，也许有更好的方法可以做到，但这是我的两分钱，注意分组列名在 MySQL 中是不可能的，因为只有列名和列名的关系是1:1.

This is quite tricky, maybe there's a better way to do it but here's my two cents, note that the grouped column names is impossible in MySQL as there is only the relationship between columns and column names is 1:1.

下面是一些代码，它生成测试数据，然后建立一个过程，该过程依赖于动态 SQL 来生成返回所需结果所需的查询，该查询也是动态执行的.请注意，代码运行时应该不会出错，但对 DROP 语句进行注释以防止对现有结构造成不必要的损坏.过程 RANDBETWEEN 在最终存储过程中不需要，但用于生成测试数据.

Below is some code, It generates test data then builds up a procedure that relies on dynamic SQL to generate the query necessary to return the results you require which is also executed dynamically. Note that the code should run without error but DROP statements are commented to prevent unwanted damage to your existing structure. The procedure RANDBETWEEN isn't needed in the final stored procedure but is used for generating test data.

#DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
      `model` varchar(64) DEFAULT NULL,
      `price` int DEFAULT NULL,
      `quantity` int(2) DEFAULT NULL,
      `date` datetime
    );

SET @VMinModel := 1;
SET @VMaxModel := 5;

SET @VMinPrice := 10;
SET @VMaxPrice := 100;

SET @VMinQuantity := 1;
SET @VMaxQuantity := 10;

SET @VMinTime := UNIX_TIMESTAMP('2016-01-01');
SET @VMaxTime := UNIX_TIMESTAMP('2016-12-31');

#DROP FUNCTION RANDBETWEEN;

DELIMITER $

CREATE FUNCTION RANDBETWEEN(VMin INTEGER UNSIGNED, VMax INTEGER UNSIGNED) RETURNS INTEGER UNSIGNED
DETERMINISTIC
BEGIN

    RETURN ROUND(RAND() * (VMax - VMin) + VMin);

END$

DELIMITER ;

INSERT INTO `sales` SELECT RANDBETWEEN(@VMinModel, @VMaxModel), RANDBETWEEN(@VMinPrice, @VMaxPrice), RANDBETWEEN(@VMinQuantity, @VMaxQuantity), FROM_UNIXTIME(RANDBETWEEN(@VMinTime, @VMaxTime)) FROM information_schema.COLUMNS LIMIT 1000;

SET @VXDimension := 'model';
SET @VYDimension := 'DATE_FORMAT(date, ''%Y-%m'')';
SET @VMeasures := 'quantity, quantity * price';
SET @VTable := 'sales';

#DROP PROCEDURE IF EXISTS singleDimensionMeltAndCast;

DELIMITER $

CREATE PROCEDURE singleDimensionMeltAndCast (VXDimension CHAR(255), VYDimension CHAR(255), VMeasures CHAR(255), VTable CHAR(255)) 
BEGIN

    SET SESSION group_concat_max_len = 65536;

    DROP TEMPORARY TABLE IF EXISTS YDimensionValues;
    SET @VCreateYDimensionValueTable := CONCAT('CREATE TEMPORARY TABLE YDimensionValues SELECT DISTINCT ', VYDimension, ' AS YDim1 FROM ', VTable, ' ORDER BY ', VYDimension, ' ASC;');

    PREPARE stmt FROM @VCreateYDimensionValueTable;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DROP TEMPORARY TABLE IF EXISTS Measures;
    CREATE TEMPORARY TABLE Measures (measure CHAR(255));
    SET @VCreateMeasuresTable := CONCAT('INSERT INTO Measures VALUES(''', REPLACE(VMeasures, ', ', '''), ('''), ''')');

    PREPARE stmt FROM @VCreateMeasuresTable;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @VYDimensionSQLFieldsComponent = (SELECT GROUP_CONCAT('SUM(IF(', VYDimension, ' = ''', YDim1, ''', ', measure, ', 0)) `[', YDim1, ' x ',  measure, ']`' SEPARATOR ', ') FROM YDimensionValues, Measures WHERE TRUE);
    SET @VYDimensionSQLGroupComponent = (SELECT GROUP_CONCAT('IF(', VYDimension, ' = ''', YDim1, ''', ', measure, ', 0)' SEPARATOR ', ') FROM YDimensionValues, Measures WHERE TRUE);

    SET @VFinalQuery := CONCAT('SELECT ', VXDimension, ', ', @VYDimensionSQLFieldsComponent, ' FROM ', VTable, ' GROUP BY ', @VYDimensionSQLGroupComponent, ' ORDER BY ', VXDimension, ';');
    PREPARE stmt FROM @VFinalQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$

DELIMITER ;

CALL singleDimensionMeltAndCast(@VXDimension, @VYDimension, @VMeasures, @VTable);

如果您有任何问题，请告诉我.

Let me know if you have any questions.

问候，

詹姆斯