SQL Server中如何将用户单状态列转为多标志列并去除重复用户行

SQL Server实现方案 #

假设你的源表名为User_Status，包含User_ID和Status两列，以下两种方式都可以得到你需要的结果：

方案1：CASE表达式 + GROUP BY聚合（推荐，逻辑清晰兼容性高） #

SELECT
    User_ID,
    Healthy = CASE WHEN COUNT(CASE WHEN Status IN ('Healthy', 'Sick') THEN 1 END) > 0 THEN 'Yes' ELSE 'No' END,
    Rich = CASE WHEN COUNT(CASE WHEN Status = 'Rich' THEN 1 END) > 0 THEN 'Yes' ELSE 'No' END,
    Studied = CASE WHEN COUNT(CASE WHEN Status = 'Studied' THEN 1 END) > 0 THEN 'Yes' ELSE 'No' END
FROM User_Status
GROUP BY User_ID
ORDER BY User_ID;

逻辑说明：按User_ID分组后，对每个用户的状态做匹配计数，符合对应条件的计数大于0则说明存在对应属性，返回Yes，否则返回No。

方案2：PIVOT行转列运算符实现 #

SELECT
    User_ID,
    Healthy = CASE WHEN ISNULL(Healthy, 0) + ISNULL(Sick, 0) > 0 THEN 'Yes' ELSE 'No' END,
    Rich = ISNULL(Rich, 'No'),
    Studied = ISNULL(Studied, 'No')
FROM (
    SELECT User_ID, Status, 'Yes' AS Flag
    FROM User_Status
) AS SourceTable
PIVOT (
    MAX(Flag)
    FOR Status IN ([Healthy], [Sick], [Rich], [Studied])
) AS PivotTable
ORDER BY User_ID;

逻辑说明：先给源数据新增固定值为Yes的标志列，通过PIVOT运算符将Status的不同取值转为列，无对应状态的列值为NULL，最后通过判断将NULL替换为No、同时合并健康相关状态的结果即可。

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