多行合并为单列：NVARCHAR列MAX聚合结果异常求助

首先，你的原始SQL存在语法错误——子查询中使用了聚合函数MAX()但没有对ID进行分组，这会导致数据库无法关联聚合结果和ID列，大概率会直接报错。我们先修正这个基础问题，再解决MAX()返回'unknown'的核心问题。

修正后的基础查询 #

如果你的需求是针对ID='0001_11'，获取每个Item列的聚合值，最简洁的写法是直接在主查询中分组并过滤：

SELECT 
    ID, 
    MAX(Item1) AS Item1, 
    MAX(Item2) AS Item2, 
    MAX(Item3) AS Item3, 
    MAX(Item4) AS Item4, 
    MAX(Item5) AS Item5
FROM Sample_Table WITH(NOLOCK)
WHERE ID = '0001_11'
GROUP BY ID

为什么会返回'unknown'？ #

MAX()函数对NVARCHAR类型的列是按照字符串字典序来比较取值的。如果你的数据中某个Item列存在'unknown'这个值，而它的字典序比该列其他所有值都大（比如其他值是数字字符串、或者以字母a-t开头的字符串），那么MAX()就会优先返回'unknown'。

根据需求调整查询 #

需求1：排除'unknown'，取其他非空值的最大值 #

如果你想忽略'unknown'，只聚合其他有效数据，可以用CASE语句过滤：

SELECT 
    ID, 
    MAX(CASE WHEN Item1 != 'unknown' THEN Item1 END) AS Item1, 
    MAX(CASE WHEN Item2 != 'unknown' THEN Item2 END) AS Item2, 
    MAX(CASE WHEN Item3 != 'unknown' THEN Item3 END) AS Item3, 
    MAX(CASE WHEN Item4 != 'unknown' THEN Item4 END) AS Item4, 
    MAX(CASE WHEN Item5 != 'unknown' THEN Item5 END) AS Item5
FROM Sample_Table WITH(NOLOCK)
WHERE ID = '0001_11'
GROUP BY ID

如果该列所有值都是'unknown'或NULL，这个查询会返回NULL，而不是'unknown'。

需求2：拼接同一ID下的所有非空Item值 #

如果你的目标是把同一ID下的每个Item列的所有非空值拼接起来（而不是取最大值），可以用字符串聚合函数，比如SQL Server的STRING_AGG：

SELECT 
    ID, 
    STRING_AGG(Item1, ', ') AS Item1, 
    STRING_AGG(Item2, ', ') AS Item2, 
    STRING_AGG(Item3, ', ') AS Item3, 
    STRING_AGG(Item4, ', ') AS Item4, 
    STRING_AGG(Item5, ', ') AS Item5
FROM Sample_Table WITH(NOLOCK)
WHERE ID = '0001_11'
  AND Item1 != 'unknown' -- 可选：排除unknown值
GROUP BY ID

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