MySQL行转列：如何将含重复问题组的表单数据转为指定宽表格式

完整查询语句（MySQL 8.0+ 版本，支持窗口函数） #

SELECT 
    g.formID,
    -- 仅首行展示基础问题
    CASE WHEN g.group_num = 1 THEN b.question1 ELSE '' END AS question1,
    CASE WHEN g.group_num = 1 THEN b.question2 ELSE '' END AS question2,
    CASE WHEN g.group_num = 1 THEN b.question3 ELSE '' END AS question3,
    g.question4,
    g.question5,
    g.question6
FROM (
    -- 动态重复问题组处理子查询
    SELECT 
        formID,
        group_num,
        MAX(CASE WHEN Questions = 'question4' THEN Answers END) AS question4,
        MAX(CASE WHEN Questions = 'question5' THEN Answers END) AS question5,
        MAX(CASE WHEN Questions = 'question6' THEN Answers END) AS question6
    FROM (
        SELECT 
            formID,
            Questions,
            Answers,
            -- 按formID+问题分组统计出现次数作为组号
            ROW_NUMBER() OVER (PARTITION BY formID, Questions ORDER BY itemID) AS group_num
        FROM form_data
        WHERE Questions IN ('question4','question5','question6')
    ) t
    GROUP BY formID, group_num
) g
LEFT JOIN (
    -- 基础问题提取子查询
    SELECT 
        formID,
        MAX(CASE WHEN Questions = 'question1' THEN Answers END) AS question1,
        MAX(CASE WHEN Questions = 'question2' THEN Answers END) AS question2,
        MAX(CASE WHEN Questions = 'question3' THEN Answers END) AS question3
    FROM form_data
    WHERE Questions IN ('question1','question2','question3')
    GROUP BY formID
) b ON g.formID = b.formID
ORDER BY g.formID, g.group_num;

MySQL 5.7 兼容版本（无窗口函数支持场景） #

SELECT 
    g.formID,
    CASE WHEN g.group_num = 1 THEN b.question1 ELSE '' END AS question1,
    CASE WHEN g.group_num = 1 THEN b.question2 ELSE '' END AS question2,
    CASE WHEN g.group_num = 1 THEN b.question3 ELSE '' END AS question3,
    g.question4,
    g.question5,
    g.question6
FROM (
    SELECT 
        formID,
        group_num,
        MAX(CASE WHEN Questions = 'question4' THEN Answers END) AS question4,
        MAX(CASE WHEN Questions = 'question5' THEN Answers END) AS question5,
        MAX(CASE WHEN Questions = 'question6' THEN Answers END) AS question6
    FROM (
        SELECT 
            formID,
            Questions,
            Answers,
            @group_num := IF(@prev_form = formID AND @prev_q = Questions, @group_num + 1, 1) AS group_num,
            @prev_form := formID,
            @prev_q := Questions
        FROM form_data
        CROSS JOIN (SELECT @prev_form := '', @prev_q := '', @group_num := 1) vars
        WHERE Questions IN ('question4','question5','question6')
        ORDER BY formID, Questions, itemID
    ) t
    GROUP BY formID, group_num
) g
LEFT JOIN (
    SELECT 
        formID,
        MAX(CASE WHEN Questions = 'question1' THEN Answers END) AS question1,
        MAX(CASE WHEN Questions = 'question2' THEN Answers END) AS question2,
        MAX(CASE WHEN Questions = 'question3' THEN Answers END) AS question3
    FROM form_data
    WHERE Questions IN ('question1','question2','question3')
    GROUP BY formID
) b ON g.formID = b.formID
ORDER BY g.formID, g.group_num;

使用注意事项 #

• 测试前请将SQL中的form_data替换为你实际的表名
• 若Questions字段存储的实际值不是question1这类标识，需要将CASE WHEN和WHERE条件中的对应字符串替换为实际的问题文本
• 查询返回的列顺序与要求的宽表格式完全匹配，PHP导出CSV时可直接遍历结果集按行写入，无需额外字段处理

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