如何修改MySQL行列转置查询以替换结果中的指定数值？

修改后的查询语句 #

SELECT cdcb_info.CISLO AS "cislo", 
MAX(CASE WHEN info = 'HBR' THEN 
    CASE value 
        WHEN 0 THEN 'zero' 
        WHEN 1 THEN 'one' 
    END 
END) AS "HBR", 
MAX(CASE WHEN info = 'HCD' THEN 
    CASE value 
        WHEN 3 THEN 'three' 
        WHEN 5 THEN 'five' 
    END 
END) AS "HCD" 
FROM cdcb_info 
GROUP BY cdcb_info.CISLO

实现说明 #

• 在原有行列转置的CASE逻辑内部，嵌套一层CASE语句完成数值到目标字符串的映射
• HBR列：匹配value值，将0替换为zero，1替换为one
• HCD列：匹配value值，将3替换为three，5替换为five
• 外层MAX函数保留，确保每个CISLO分组下仅返回对应列的唯一有效值

预期查询结果 #

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