MySQL行转列需求：将聚合结果转为横向多列展示

这是个典型的行转列需求，而且因为日期是动态的（可能有任意多个不同日期），所以得用动态SQL来实现——MySQL没有原生的TRANSPOSE函数，静态PIVOT也没法应对不确定的列数，我来给你拆解下解决思路和具体代码：

核心思路 #

我们需要把按日期分组后的每行数据（ts+value），转换成一行里的多组列（ts0+value0、ts1+value1...tsn+valuen）。步骤如下：

1. 先对原始数据按日期分组求和，得到基础的聚合结果。
2. 给每个不同的日期分配唯一行号，确保列的顺序和日期先后一致。
3. 用动态SQL拼接出对应的CASE WHEN语句，把每行的ts和value映射到对应的列上。

具体实现（MySQL 8.0+版本） #

-- 1. 初始化变量存储动态SQL语句
SET @sql = NULL;

-- 2. 生成需要的列定义（ts0/value0、ts1/value1...）
SELECT GROUP_CONCAT(
    CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN ts END) AS ts', rn-1, ', ',
        'MAX(CASE WHEN rn = ', rn, ' THEN value END) AS value', rn-1
    )
) INTO @sql
FROM (
    -- 给每个唯一日期分配行号，按日期排序
    SELECT ROW_NUMBER() OVER (ORDER BY created_at) AS rn
    FROM (SELECT DISTINCT created_at FROM leads_ads WHERE status NOT IN ('COMPLAINED', 'COMPLAIN_ACCEPTED')) AS dates
) AS numbered_dates;

-- 3. 拼接完整的查询语句
SET @sql = CONCAT('SELECT ', @sql, ' FROM (
    -- 先按日期分组求和，同时给每组分配行号
    SELECT 
        created_at AS ts,
        SUM(fee) AS value,
        ROW_NUMBER() OVER (ORDER BY created_at) AS rn
    FROM leads_ads 
    WHERE status NOT IN (\'COMPLAINED\', \'COMPLAIN_ACCEPTED\') 
    GROUP BY created_at
) AS aggregated_data');

-- 4. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

适配MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本低于8.0，没法用ROW_NUMBER()窗口函数，可以用用户变量来生成行号：

SET @sql = NULL;
SET @rn = 0;

-- 生成列定义
SELECT GROUP_CONCAT(
    CONCAT(
        'MAX(CASE WHEN rn = ', rn, ' THEN ts END) AS ts', rn-1, ', ',
        'MAX(CASE WHEN rn = ', rn, ' THEN value END) AS value', rn-1
    )
) INTO @sql
FROM (
    SELECT @rn := @rn + 1 AS rn, created_at
    FROM (SELECT DISTINCT created_at FROM leads_ads WHERE status NOT IN ('COMPLAINED', 'COMPLAIN_ACCEPTED')) AS dates
    ORDER BY created_at
) AS numbered_dates;

SET @rn = 0;
-- 拼接完整查询
SET @sql = CONCAT('SELECT ', @sql, ' FROM (
    SELECT 
        created_at AS ts,
        SUM(fee) AS value,
        @rn := @rn + 1 AS rn
    FROM leads_ads 
    WHERE status NOT IN (\'COMPLAINED\', \'COMPLAIN_ACCEPTED\') 
    GROUP BY created_at
    ORDER BY created_at
) AS aggregated_data');

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

关键说明 #

• 用GROUP_CONCAT拼接列定义时，如果日期数量很多，可能会超出默认长度限制，可以先执行SET SESSION group_concat_max_len = 1000000;临时调整上限。
• 用MAX()聚合函数是因为每个行号（rn）对应唯一一行数据，MAX只是用来取出这行的唯一值，不会影响结果。
• 最终结果会严格按照日期从小到大的顺序生成ts0到tsn、value0到valuen的列。

内容的提问来源于stack exchange，提问作者Osvaldas Šlapikas