SQL Server视图转置表时负数显示为0的解决方法求助

在SQL Server中创建视图转置包含正负数值的表时，所有负数都被返回为0，如何解决该问题？

原表示例 #

转置视图的错误结果 #

原视图SQL脚本 #

SELECT Peak, MAX(CASE WHEN T .YEAR = 2016 THEN T .[Value_A] ELSE 0.00 END) AS Value_A_2016, 
       MAX(CASE WHEN T .YEAR = 2018 THEN T .[Value_A] ELSE 0.00 END) AS Value_A_2018, 
       MAX(CASE WHEN T .YEAR = 2028 THEN T .[Value_A] ELSE 0.00 END) AS Value_A_2028, 
       MAX(CASE WHEN T .YEAR = 2016 THEN T .[Value_B] ELSE 0.00 END) AS Value_B_2016, 
       MAX(CASE WHEN T .YEAR = 2018 THEN T .[Value_B] ELSE 0.00 END) AS Value_B_2018, 
       MAX(CASE WHEN T .YEAR = 2028 THEN T .[Value_B] ELSE 0.00 END) AS Value_B_2028
FROM gisadmin.Table_1 AS T
GROUP BY Peak

问题原因 #

使用MAX()聚合函数时，当CASE语句匹配到负数，0的数值比负数大，MAX()会选择0而非负数，导致所有负数被替换为0。

修改后的SQL脚本 #

提供两种可行的修改方案：

方案1：使用SUM()聚合函数（推荐）

SELECT Peak, SUM(CASE WHEN T.YEAR = 2016 THEN T.[Value_A] ELSE NULL END) AS Value_A_2016, 
       SUM(CASE WHEN T.YEAR = 2018 THEN T.[Value_A] ELSE NULL END) AS Value_A_2018, 
       SUM(CASE WHEN T.YEAR = 2028 THEN T.[Value_A] ELSE NULL END) AS Value_A_2028, 
       SUM(CASE WHEN T.YEAR = 2016 THEN T.[Value_B] ELSE NULL END) AS Value_B_2016, 
       SUM(CASE WHEN T.YEAR = 2018 THEN T.[Value_B] ELSE NULL END) AS Value_B_2018, 
       SUM(CASE WHEN T.YEAR = 2028 THEN T.[Value_B] ELSE NULL END) AS Value_B_2028
FROM gisadmin.Table_1 AS T
GROUP BY Peak

方案2：保留MAX()，将ELSE改为NULL

SELECT Peak, MAX(CASE WHEN T.YEAR = 2016 THEN T.[Value_A] ELSE NULL END) AS Value_A_2016, 
       MAX(CASE WHEN T.YEAR = 2018 THEN T.[Value_A] ELSE NULL END) AS Value_A_2018, 
       MAX(CASE WHEN T.YEAR = 2028 THEN T.[Value_A] ELSE NULL END) AS Value_A_2028, 
       MAX(CASE WHEN T.YEAR = 2016 THEN T.[Value_B] ELSE NULL END) AS Value_B_2016, 
       MAX(CASE WHEN T.YEAR = 2018 THEN T.[Value_B] ELSE NULL END) AS Value_B_2018, 
       MAX(CASE WHEN T.YEAR = 2028 THEN T.[Value_B] ELSE NULL END) AS Value_B_2028
FROM gisadmin.Table_1 AS T
GROUP BY Peak

方案说明 #

• 方案1中，SUM()会忽略NULL值，若每个Peak+Year组合仅一条数据，结果等同于直接取该值；若有多条数据，会正确求和并保留负数。
• 方案2中，MAX()同样忽略NULL值，匹配到负数时，该负数是分组内的唯一有效值，因此会被正确返回而非0。

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