如何在SQL Server中使用CASE表达式格式化SQL查询输出实现行合并

解决方案 #

你当前的查询结果中每个WFH_ID的有效时段数据分散在不同行，NULL值是因为单行只对应一个时段的有效数据，只需要通过分组聚合即可合并为预期的单行结果，SQL Server中可以使用MAX聚合函数忽略NULL值的特性实现效果：

SELECT 
    WFH_Id,
    MAX(FromTime_1) AS FromTime1,
    MAX(ToTime_1) AS ToTime1,
    MAX(FromTime_2) AS FromTime2,
    MAX(ToTime_2) AS ToTime2,
    MAX(FromTime_3) AS FromTime3,
    MAX(ToTime_3) AS ToTime3,
    MAX(FromTime_4) AS FromTime4,
    MAX(ToTime_4) AS ToTime4
FROM (
    -- 此处直接保留你原有UNION查询即可
    SELECT WFH_Id
        ,CASE WHEN Slot_Count=4 AND Row_NO=1 THEN FromTime END AS FromTime_1,
        CASE WHEN Slot_Count=4 AND Row_NO=1 THEN ToTime END AS ToTime_1,
        CASE WHEN Slot_Count=4 AND Row_NO=2 THEN FromTime END AS FromTime_2,
        CASE WHEN Slot_Count=4 AND Row_NO=2 THEN ToTime END AS ToTime_2,
        CASE WHEN Slot_Count=4 AND Row_NO=3 THEN FromTime END AS FromTime_3,
        CASE WHEN Slot_Count=4 AND Row_NO=3 THEN ToTime END AS ToTime_3,
        CASE WHEN Slot_Count=4 AND Row_NO=4 THEN FromTime END AS FromTime_4,
        CASE WHEN Slot_Count=4 AND Row_NO=4 THEN ToTime END AS ToTime_4
    FROM #tempMorning 
    UNION
    SELECT WFH_Id
        ,CASE WHEN Slot_Count=3 AND Row_NO=1 THEN FromTime END AS FromTime_1,
        CASE WHEN Slot_Count=3 AND Row_NO=1 THEN ToTime END AS ToTime_1,
        CASE WHEN Slot_Count=3 AND Row_NO=2 THEN FromTime END AS FromTime_2,
        CASE WHEN Slot_Count=3 AND Row_NO=2 THEN ToTime END AS ToTime_2,
        CASE WHEN Slot_Count=3 AND Row_NO=3 THEN FromTime END AS FromTime_3,
        CASE WHEN Slot_Count=3 AND Row_NO=3 THEN ToTime END AS ToTime_3,
        CASE WHEN Slot_Count=3 AND Row_NO=4 THEN FromTime END AS FromTime_4,
        CASE WHEN Slot_Count=3 AND Row_NO=4 THEN ToTime END AS ToTime_4
    FROM #tempMorning 
    UNION
    SELECT WFH_Id
        ,CASE WHEN Slot_Count=2 AND Row_NO=1 THEN FromTime END AS FromTime_1,
        CASE WHEN Slot_Count=2 AND Row_NO=1 THEN ToTime END AS ToTime_1,
        CASE WHEN Slot_Count=2 AND Row_NO=2 THEN FromTime END AS FromTime_2,
        CASE WHEN Slot_Count=2 AND Row_NO=2 THEN ToTime END AS ToTime_2,
        CASE WHEN Slot_Count=2 AND Row_NO=3 THEN FromTime END AS FromTime_3,
        CASE WHEN Slot_Count=2 AND Row_NO=3 THEN ToTime END AS ToTime_3,
        CASE WHEN Slot_Count=2 AND Row_NO=4 THEN FromTime END AS FromTime_4,
        CASE WHEN Slot_Count=2 AND Row_NO=4 THEN ToTime END AS ToTime_4
    FROM #tempMorning 
    UNION
    SELECT WFH_Id
        ,CASE WHEN Slot_Count=1 AND Row_NO=1 THEN FromTime END AS FromTime_1,
        CASE WHEN Slot_Count=1 AND Row_NO=1 THEN ToTime END AS ToTime_1,
        CASE WHEN Slot_Count=1 AND Row_NO=2 THEN FromTime END AS FromTime_2,
        CASE WHEN Slot_Count=1 AND Row_NO=2 THEN ToTime END AS ToTime_2,
        CASE WHEN Slot_Count=1 AND Row_NO=3 THEN FromTime END AS FromTime_3,
        CASE WHEN Slot_Count=1 AND Row_NO=3 THEN ToTime END AS ToTime_3,
        CASE WHEN Slot_Count=1 AND Row_NO=4 THEN FromTime END AS FromTime_4,
        CASE WHEN Slot_Count=1 AND Row_NO=4 THEN ToTime END AS ToTime_4
    FROM #tempMorning 
) AS t
GROUP BY WFH_Id

你也可以直接简化原有逻辑，不需要多次查询同一张临时表，性能更优：

SELECT 
    WFH_Id,
    MAX(CASE WHEN Row_NO=1 THEN FromTime END) AS FromTime1,
    MAX(CASE WHEN Row_NO=1 THEN ToTime END) AS ToTime1,
    MAX(CASE WHEN Row_NO=2 THEN FromTime END) AS FromTime2,
    MAX(CASE WHEN Row_NO=2 THEN ToTime END) AS ToTime2,
    MAX(CASE WHEN Row_NO=3 THEN FromTime END) AS FromTime3,
    MAX(CASE WHEN Row_NO=3 THEN ToTime END) AS ToTime3,
    MAX(CASE WHEN Row_NO=4 THEN FromTime END) AS FromTime4,
    MAX(CASE WHEN Row_NO=4 THEN ToTime END) AS ToTime4
FROM #tempMorning
GROUP BY WFH_Id

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