如何编写Case Statement处理Answer列NULL值并拼接非空答案

核心要按EncounterID分组处理，完成两个关键逻辑：判断分组内是否存在NULL的Answer，再对非空值按要求拼接展示。

核心规则 #

• 若分组内存在NULL的Answer：仅处理非空值
  • 非空值数量为1时，直接返回该值
  • 非空值数量大于1时，按「值1 And 值2」（多值则为「值1, 值2 And 值3」）的格式拼接
• 若分组内无NULL值：可按需选择直接返回单个值，或用逗号拼接所有值

SQL Server 示例代码（兼容全版本） #

SELECT 
    EncounterID,
    CASE
        -- 判断当前分组是否存在NULL的Answer
        WHEN COUNT(CASE WHEN Answer IS NULL THEN 1 END) > 0 THEN
            CASE
                -- 仅1个非空值时直接返回
                WHEN COUNT(Answer) = 1 THEN MAX(Answer)
                -- 多个非空值时拼接成指定格式
                ELSE STUFF(
                    (SELECT ', ' + Answer 
                     FROM YourTableName t2 
                     WHERE t2.EncounterID = t1.EncounterID AND t2.Answer IS NOT NULL
                     ORDER BY QuestionID
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
                    1, 2, ''
                ) + ' And ' + (
                    SELECT TOP 1 Answer 
                    FROM YourTableName t2 
                    WHERE t2.EncounterID = t1.EncounterID AND t2.Answer IS NOT NULL
                    ORDER BY QuestionID DESC
                )
            END
        -- 无NULL值时的处理逻辑，可按需调整
        ELSE
            CASE
                WHEN COUNT(Answer) = 1 THEN MAX(Answer)
                ELSE STUFF(
                    (SELECT ', ' + Answer 
                     FROM YourTableName t2 
                     WHERE t2.EncounterID = t1.EncounterID
                     ORDER BY QuestionID
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
                    1, 2, ''
                )
            END
    END AS ProcessedAnswer
FROM YourTableName t1
GROUP BY EncounterID;

SQL Server 2017+ 简化版本（用STRING_AGG） #

SELECT 
    EncounterID,
    CASE
        WHEN COUNT(CASE WHEN Answer IS NULL THEN 1 END) > 0 THEN
            CASE
                WHEN COUNT(Answer) = 1 THEN MAX(Answer)
                ELSE CONCAT(
                    STRING_AGG(Answer, ', ') WITHIN GROUP (ORDER BY QuestionID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),
                    ' And ',
                    LAST_VALUE(Answer) OVER (PARTITION BY EncounterID ORDER BY QuestionID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                )
            END
        ELSE
            CASE
                WHEN COUNT(Answer) = 1 THEN MAX(Answer)
                ELSE STRING_AGG(Answer, ', ') WITHIN GROUP (ORDER BY QuestionID)
            END
    END AS ProcessedAnswer
FROM YourTableName
WHERE Answer IS NOT NULL -- 存在NULL时仅取非空值
GROUP BY EncounterID;

示例数据验证 #

针对你给出的示例数据（EncounterID=1234分组存在NULL，2个非空值），执行代码后会输出Clark Kent And Lois Lane，完全符合需求。

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