如何在MySQLi查询中将行转换为列（月份数据转列需求）

没问题，我来帮你搞定这个行转列的需求！针对你当前的查询场景，MySQL里常用两种方式来实现行转列，分别适合不同的使用场景，下面给你详细拆解：

一、静态列转换（固定月份范围） #

如果你能确定数据涉及的月份范围（比如全年12个月，或者某几个特定月份），用CASE WHEN配合聚合函数就能快速实现。这里以全年12个月为例，把每个月份作为单独的列展示：

SELECT
    ME.`MethodName`,
    -- 逐个定义月份列，统计对应月份的新用户数
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 1 THEN 1 ELSE 0 END) AS 'Jan-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 2 THEN 1 ELSE 0 END) AS 'Feb-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 3 THEN 1 ELSE 0 END) AS 'Mar-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 4 THEN 1 ELSE 0 END) AS 'Apr-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 5 THEN 1 ELSE 0 END) AS 'May-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 6 THEN 1 ELSE 0 END) AS 'Jun-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 7 THEN 1 ELSE 0 END) AS 'Jul-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 8 THEN 1 ELSE 0 END) AS 'Aug-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 9 THEN 1 ELSE 0 END) AS 'Sep-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 10 THEN 1 ELSE 0 END) AS 'Oct-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 11 THEN 1 ELSE 0 END) AS 'Nov-2024',
    SUM(CASE WHEN MONTH(C.ClientServiceDate) = 12 THEN 1 ELSE 0 END) AS 'Dec-2024',
    -- 可选：添加总计列，统计该方法的总新用户数
    COUNT(C.`ContraceptiveMethod`) AS 'TotalUsers'
FROM mwra AS M
JOIN client_information AS C ON (C.MwraId = M.MwraId)
LEFT JOIN methods AS ME ON (ME.MethodId = C.ContraceptiveMethod)
WHERE C.FpUserStatus = 'New'
-- 仅按避孕方法分组，不再按月份拆分
GROUP BY ME.MethodId, ME.`MethodName`;

核心细节： #

• CASE WHEN用来判断当前行数据是否属于目标月份，符合条件标记为1，否则为0，再用SUM聚合得到该月份的用户数
• 列名可以根据实际年份修改，比如把Jan-2024改成你需要的年月格式
• 如果只需要特定几个月份，删掉不需要的SUM(CASE...)语句即可

二、动态列转换（月份不固定） #

如果你的数据涉及的年月是动态变化的（比如不确定会有哪些年月的记录），可以用MySQL存储过程自动识别所有存在的月份，动态生成行转列的SQL：

DELIMITER //
CREATE PROCEDURE PivotMonthlyMethodUsers()
BEGIN
    -- 第一步：自动生成所有需要转成列的年月对应的SQL片段
    SET @cols = NULL;
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN MONTH(C.ClientServiceDate) = ', MONTH(C.ClientServiceDate), ' AND YEAR(C.ClientServiceDate) = ', YEAR(C.ClientServiceDate), ' THEN 1 ELSE 0 END) AS `', CONCAT(MONTHNAME(C.ClientServiceDate), '-', YEAR(C.ClientServiceDate)), '`')
    ) INTO @cols
    FROM client_information AS C
    WHERE C.FpUserStatus = 'New';

    -- 第二步：拼接完整的查询语句
    SET @sql = CONCAT('
        SELECT ME.`MethodName`, ', @cols, ', COUNT(C.`ContraceptiveMethod`) AS TotalUsers
        FROM mwra AS M
        JOIN client_information AS C ON (C.MwraId = M.MwraId)
        LEFT JOIN methods AS ME ON (ME.MethodId = C.ContraceptiveMethod)
        WHERE C.FpUserStatus = ''New''
        GROUP BY ME.MethodId, ME.`MethodName`
    ');

    -- 第三步：执行动态生成的SQL
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

使用方式： #

调用这个存储过程就能自动生成对应年月的列：

CALL PivotMonthlyMethodUsers();

核心细节： #

• GROUP_CONCAT会自动遍历所有符合条件的年月，拼接出对应的CASE WHEN语句
• 存储过程会自动适配数据中存在的所有年月，无需手动指定
• 如果需要调整列名格式，修改CONCAT(MONTHNAME(...), '-', YEAR(...))部分即可

内容的提问来源于stack exchange，提问作者Adeel Ahmed Baloch