如何将SQL中同一person id的多行记录合并为单行展示？

原始表结构及数据 #

错误的查询尝试 #

我曾使用CASE语句和GROUP BY子句，但得到了多余的记录，查询语句如下：

SELECT DISTINCT
F.MEMBER
,F.GIVEN_NAMES
,F.SURNAME
--VALUES NEEDED
,CASE WHEN F.VALUE_NEEDED = 'Postal Address' THEN 'Yes' ELSE '' END POSTAL_ADDRESS
,CASE WHEN F.VALUE_NEEDED = 'Birthday' THEN 'Yes' ELSE '' END BIRTHDAY
,CASE WHEN F.VALUE_NEEDED = 'Email Address' THEN 'Yes' ELSE '' END EMAIL_ADDRESS
,CASE WHEN F.VALUE_NEEDED = 'First Name' THEN 'Yes' ELSE '' END FIRST_NAME
,CASE WHEN F.VALUE_NEEDED = 'Surname' THEN 'Yes' ELSE '' END SURNAME
,CASE WHEN F.VALUE_NEEDED = 'Title and Gender' THEN 'Yes' ELSE '' END 'TITLE|GENDER'
,CASE WHEN F.VALUE_NEEDED = 'Mobile' THEN 'Yes' ELSE '' END MOBILE
,CASE WHEN F.VALUE_NEEDED = 'Beneficiary' THEN 'Yes' ELSE '' END BENEFICIARY
FROM #FINAL F
GROUP BY F.MEMBER,F.GIVEN_NAMES
,F.SURNAME,VALUE_NEEDED
ORDER BY F.MEMBER

错误结果 #

期望结果 #

正确的SQL查询写法 #

问题核心是GROUP BY中包含了VALUE_NEEDED（对应示例中的fruit字段），导致每个不同的fruit值都会生成单独一行。需要用聚合函数（如MAX()）包裹CASE语句，同时GROUP BY只保留用户唯一标识字段。

针对水果表场景的正确查询 #

SELECT 
    [person id],
    MAX(CASE WHEN fruit = 'apple' THEN 'yes' ELSE '' END) AS apple,
    MAX(CASE WHEN fruit = 'orange' THEN 'yes' ELSE '' END) AS orange,
    MAX(CASE WHEN fruit = 'banana' THEN 'yes' ELSE '' END) AS banana
FROM 
    你的表名
GROUP BY 
    [person id]
ORDER BY 
    [person id]

针对你实际使用的#FINAL表场景的修正查询 #

SELECT 
    F.MEMBER,
    F.GIVEN_NAMES,
    F.SURNAME,
    MAX(CASE WHEN F.VALUE_NEEDED = 'Postal Address' THEN 'Yes' ELSE '' END) AS POSTAL_ADDRESS,
    MAX(CASE WHEN F.VALUE_NEEDED = 'Birthday' THEN 'Yes' ELSE '' END) AS BIRTHDAY,
    MAX(CASE WHEN F.VALUE_NEEDED = 'Email Address' THEN 'Yes' ELSE '' END) AS EMAIL_ADDRESS,
    MAX(CASE WHEN F.VALUE_NEEDED = 'First Name' THEN 'Yes' ELSE '' END) AS FIRST_NAME,
    MAX(CASE WHEN F.VALUE_NEEDED = 'Surname' THEN 'Yes' ELSE '' END) AS SURNAME,
    MAX(CASE WHEN F.VALUE_NEEDED = 'Title and Gender' THEN 'Yes' ELSE '' END) AS [TITLE|GENDER],
    MAX(CASE WHEN F.VALUE_NEEDED = 'Mobile' THEN 'Yes' ELSE '' END) AS MOBILE,
    MAX(CASE WHEN F.VALUE_NEEDED = 'Beneficiary' THEN 'Yes' ELSE '' END) AS BENEFICIARY
FROM 
    #FINAL F
GROUP BY 
    F.MEMBER, F.GIVEN_NAMES, F.SURNAME
ORDER BY 
    F.MEMBER

关键说明 #

1. MAX()聚合函数会提取同一用户分组下CASE语句的非空'Yes'值，实现多行合并
2. GROUP BY中移除VALUE_NEEDED（或fruit），仅保留用户唯一标识字段，确保每个用户仅生成一行记录
3. 无需使用DISTINCT，GROUP BY已完成分组去重

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