MySQL动态行转列查询需求：可变Key的KV表转每日指标格式

问题背景 #

我们有一个存储每日KPI的键值对元数据表，结构如下：

+------+---------+-------------+------------+ 
| id   | key     | value       | last_update| 
+------+---------+-------------+------------+ 
|    1 | key1    | foo         | 2022-08-08 | 
|    2 | key2    | bar         | 2022-08-08 | 
|    3 | key4    | more        | 2022-08-08 | 
|    4 | key2    | galaxy      | 2022-08-07 | 
|    5 | key3    | foo         | 2022-08-06 | 
|    6 | key4    | other       | 2022-08-06 | 
+------+---------+-------------+------------+ 

该表仅保存与上一次值不同的数据，因此并非所有Key每天都会生成，且新Key可能随时出现。为满足图表展示需求，需编写MySQL查询语句，将数据转置为每行对应一天、包含当日所有Key的传统格式，期望输出如下：

+---------+----------+---------+--------+------------+
| key1    | key2     | key3    | key4   | date       |
+---------+----------+---------+--------+------------+
| foo     | bar      | NULL    | more   | 2022-08-08 |
| NULL    | galaxy   | NULL    | NULL   | 2022-08-07 |
| NULL    | NULL     | foo     | other  | 2022-08-06 |
+---------+----------+---------+--------+------------+ 

曾尝试基于SELECT DISTINCT id FROM source_table ORDER BY id的方案，但未成功。

补充说明：直接使用固定Key的查询方案无效，因为Key不固定且会新增/变更，想了解是否可借助每行对应一个Key的key_reference表实现需求。

解决方案 #

由于MySQL静态SQL无法处理动态变化的列名，必须结合动态SQL和key_reference表（或临时生成的Key集合）来实现转置，具体步骤如下：

1. 准备Key集合（借助key_reference表） #

如果已有key_reference表，确保它包含所有已存在和新增的Key；如果没有，可以临时生成：

-- 创建临时key_reference表，提取原表所有唯一Key
CREATE TEMPORARY TABLE key_reference AS
SELECT DISTINCT `key` FROM kpi_table ORDER BY `key`;

2. 编写动态转置存储过程 #

通过存储过程动态拼接列名并执行转置查询：

DELIMITER //
CREATE PROCEDURE pivot_kpi_data()
BEGIN
    -- 拼接所有Key对应的CASE语句，生成列片段
    SET @cols = NULL;
    SELECT GROUP_CONCAT(DISTINCT CONCAT(
        'MAX(CASE WHEN `key` = ''', `key`, ''' THEN value END) AS ', `key`
    )) INTO @cols FROM key_reference;

    -- 构造完整的转置SQL语句
    SET @sql = CONCAT(
        'SELECT ', @cols, ', last_update AS date ',
        'FROM kpi_table ',
        'GROUP BY last_update ',
        'ORDER BY last_update DESC'
    );

    -- 执行动态SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

3. 执行查询 #

调用存储过程即可得到期望的转置结果：

CALL pivot_kpi_data();

关键说明 #

• 转置逻辑：用MAX(CASE...)实现行转列，因为每个last_update+key组合仅存一条记录，MAX/MIN/SUM均可达到效果，这里使用MAX是常规选择。
• key_reference表作用：统一管理所有Key，若有新Key加入，只需更新该表（或临时查询时从原表提取最新Key集合）即可适配新列。
• 动态SQL必要性：静态SQL无法提前预知所有Key，必须通过动态拼接列名来实现灵活的转置。

内容的提问来源于stack exchange，提问作者luison