Azure Synapse无服务器版多列拼接异常：CASE WHEN未生效

核心问题 #

你的代码存在两个关键问题导致拼接失效：

1. 部分分支未统一转换UniqueIdentifier类型：部分CONCAT_WS中的列未显式转换为字符串，Azure Synapse Serverless中UniqueIdentifier类型与字符串拼接时，隐式转换可能导致拼接异常或仅返回单个值。
2. CASE分支逻辑与预期不符：WHEN EnfantGuid_3 IS NOT NULL分支的拼接顺序颠倒，且部分分支缺失（如未处理仅EnfantGuid_2非空的场景），同时类型转换不统一导致结果不符合预期。

修正后的代码（匹配原CASE逻辑） #

如果你的需求是：根据最右侧非空的EnfantGuid_n，拼接其左侧所有列（如EnfantGuid_3非空则拼接1+2），使用以下代码：

SELECT 
    Categorie, ParentGuid,
    COALESCE(CAST(EnfantGuid_7 AS nvarchar(1000)),
             CAST(EnfantGuid_6 AS nvarchar(1000)), 
             CAST(EnfantGuid_5 AS nvarchar(1000)), 
             CAST(EnfantGuid_4 AS nvarchar(1000)), 
             CAST(EnfantGuid_3 AS nvarchar(1000)), 
             CAST(EnfantGuid_2 AS nvarchar(1000)),
             CAST(EnfantGuid_1 AS nvarchar(1000))) EnfantGuid,
    CASE 
        WHEN EnfantGuid_7 IS NOT NULL 
            THEN CONCAT_WS(', ', 
                CAST(EnfantGuid_1 AS nvarchar(1000)), 
                CAST(EnfantGuid_2 AS nvarchar(1000)), 
                CAST(EnfantGuid_3 AS nvarchar(1000)), 
                CAST(EnfantGuid_4 AS nvarchar(1000)), 
                CAST(EnfantGuid_5 AS nvarchar(1000)), 
                CAST(EnfantGuid_6 AS nvarchar(1000))
            )
        WHEN EnfantGuid_6 IS NOT NULL 
            THEN CONCAT_WS(', ', 
                CAST(EnfantGuid_1 AS nvarchar(1000)), 
                CAST(EnfantGuid_2 AS nvarchar(1000)), 
                CAST(EnfantGuid_3 AS nvarchar(1000)), 
                CAST(EnfantGuid_4 AS nvarchar(1000)), 
                CAST(EnfantGuid_5 AS nvarchar(1000))
            )
        WHEN EnfantGuid_5 IS NOT NULL 
            THEN CONCAT_WS(', ',
                CAST(EnfantGuid_1 AS nvarchar(1000)), 
                CAST(EnfantGuid_2 AS nvarchar(1000)), 
                CAST(EnfantGuid_3 AS nvarchar(1000)), 
                CAST(EnfantGuid_4 AS nvarchar(1000))
            )
        WHEN EnfantGuid_4 IS NOT NULL 
            THEN CONCAT_WS(', ',
                CAST(EnfantGuid_1 AS nvarchar(1000)), 
                CAST(EnfantGuid_2 AS nvarchar(1000)), 
                CAST(EnfantGuid_3 AS nvarchar(1000))
            )
        WHEN EnfantGuid_3 IS NOT NULL 
            THEN CONCAT_WS(', ', 
                CAST(EnfantGuid_1 AS nvarchar(1000)), 
                CAST(EnfantGuid_2 AS nvarchar(1000))
            )
        WHEN EnfantGuid_2 IS NOT NULL 
            THEN CAST(EnfantGuid_1 AS nvarchar(1000))
        ELSE CAST(EnfantGuid_1 AS nvarchar(1000))
    END AS [Path],  
    EnfantGuid_1, EnfantGuid_2, EnfantGuid_3, EnfantGuid_4, EnfantGuid_5, EnfantGuid_6, EnfantGuid_7

关键修正点 #

• 所有CONCAT_WS中的UniqueIdentifier列均显式转换为nvarchar(1000)，确保类型统一，避免拼接异常。
• 调整WHEN EnfantGuid_3 IS NOT NULL分支的拼接顺序，将EnfantGuid_1放在前面，符合你预期的拼接结果。
• 新增WHEN EnfantGuid_2 IS NOT NULL分支，完善逻辑覆盖场景。
• ELSE分支也统一转换为字符串，保持结果类型一致。

更简洁的替代方案（拼接所有非空列） #

如果你的需求是直接拼接所有非空的EnfantGuid_1到EnfantGuid_7，无需CASE判断，CONCAT_WS会自动跳过NULL值，代码更简洁：

SELECT 
    Categorie, ParentGuid,
    COALESCE(CAST(EnfantGuid_7 AS nvarchar(1000)),
             CAST(EnfantGuid_6 AS nvarchar(1000)), 
             CAST(EnfantGuid_5 AS nvarchar(1000)), 
             CAST(EnfantGuid_4 AS nvarchar(1000)), 
             CAST(EnfantGuid_3 AS nvarchar(1000)), 
             CAST(EnfantGuid_2 AS nvarchar(1000)),
             CAST(EnfantGuid_1 AS nvarchar(1000))) EnfantGuid,
    CONCAT_WS(', ', 
        CAST(EnfantGuid_1 AS nvarchar(1000)),
        CAST(EnfantGuid_2 AS nvarchar(1000)),
        CAST(EnfantGuid_3 AS nvarchar(1000)),
        CAST(EnfantGuid_4 AS nvarchar(1000)),
        CAST(EnfantGuid_5 AS nvarchar(1000)),
        CAST(EnfantGuid_6 AS nvarchar(1000)),
        CAST(EnfantGuid_7 AS nvarchar(1000))
    ) AS [Path],  
    EnfantGuid_1, EnfantGuid_2, EnfantGuid_3, EnfantGuid_4, EnfantGuid_5, EnfantGuid_6, EnfantGuid_7

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