如何将同一ID关联行合并为一行？含NULL值处理（SQL Server 2014）

原始数据 #

期望结果 #

结果1：保留NULL文本 #

结果2：替换NULL为空字符串 #

使用版本 #

Microsoft SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64)

尝试的查询 #

SELECT IDENT_0, PAYS_0 = STUFF

SELECT ', ' + TEXTE_0
FROM UAI.YORIGINELOT AS T2
LEFT JOIN UAI.ATEXTRA ON T2.YOMP_0 = ATEXTRA.IDENT1_0 AND CODFIC_0 = 'TABCOUNTRY' AND LANGUE_0 = 'FRA' And ZONE_0 = 'CRYDES'
WHERE T2.IDENT_0 = T1.IDENT_0
ORDER BY IDENT_0
FOR XML PATH (''), TYPE
     ).value('.', 'varchar(max)')
1, 1, '')
FROM UAI.YORIGINELOT AS T1
LEFT JOIN UAI.ATEXTRA ON T1.YFABEN_0 = ATEXTRA.IDENT1_0 AND LANGUE_0='FRA' AND CODFIC_0='TABCOUNTRY' AND ZONE_0 = 'CRYDES'
WHERE OBJ_0 = 'ITM'  
GROUP BY IDENT_0

问题分析与解决方案 #

你的查询存在两个核心问题：

1. STUFF函数语法错误：缺少包裹子查询的左括号，导致语法不合法。
2. NULL值未处理：当TEXTE_0为NULL时，', ' + TEXTE_0会返回NULL，这部分内容会被跳过，无法出现在合并结果中。

针对两种期望结果，分别提供修正后的查询：

方案1：保留NULL为文本"NULL" #

SELECT 
    T1.IDENT_0,
    PAYS_0 = STUFF(
        (
            SELECT ',' + ISNULL(ATEXTRA.TEXTE_0, 'NULL')
            FROM UAI.YORIGINELOT AS T2
            LEFT JOIN UAI.ATEXTRA 
                ON T2.YOMP_0 = ATEXTRA.IDENT1_0 
                AND CODFIC_0 = 'TABCOUNTRY' 
                AND LANGUE_0 = 'FRA' 
                AND ZONE_0 = 'CRYDES'
            WHERE T2.IDENT_0 = T1.IDENT_0
            ORDER BY T2.IDENT_0 -- 可根据实际需求调整排序字段
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'),
        1, 1, ''
    )
FROM UAI.YORIGINELOT AS T1
LEFT JOIN UAI.ATEXTRA 
    ON T1.YFABEN_0 = ATEXTRA.IDENT1_0 
    AND LANGUE_0='FRA' 
    AND CODFIC_0='TABCOUNTRY' 
    AND ZONE_0 = 'CRYDES'
WHERE OBJ_0 = 'ITM'  
GROUP BY T1.IDENT_0;

方案2：替换NULL为空字符串 #

只需将ISNULL(ATEXTRA.TEXTE_0, 'NULL')改为ISNULL(ATEXTRA.TEXTE_0, '')即可：

SELECT 
    T1.IDENT_0,
    PAYS_0 = STUFF(
        (
            SELECT ',' + ISNULL(ATEXTRA.TEXTE_0, '')
            FROM UAI.YORIGINELOT AS T2
            LEFT JOIN UAI.ATEXTRA 
                ON T2.YOMP_0 = ATEXTRA.IDENT1_0 
                AND CODFIC_0 = 'TABCOUNTRY' 
                AND LANGUE_0 = 'FRA' 
                AND ZONE_0 = 'CRYDES'
            WHERE T2.IDENT_0 = T1.IDENT_0
            ORDER BY T2.IDENT_0 -- 可根据实际需求调整排序字段
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'),
        1, 1, ''
    )
FROM UAI.YORIGINELOT AS T1
LEFT JOIN UAI.ATEXTRA 
    ON T1.YFABEN_0 = ATEXTRA.IDENT1_0 
    AND LANGUE_0='FRA' 
    AND CODFIC_0='TABCOUNTRY' 
    AND ZONE_0 = 'CRYDES'
WHERE OBJ_0 = 'ITM'  
GROUP BY T1.IDENT_0;

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