sql - 使用 COALESCE 函数在变量中附加消息

Question

下面是我的代码。

DECLARE @msg NVARCHAR(MAX) = NULL

;WITH CTE AS (
                    SELECT 'A' AS Message
                UNION
                    SELECT 'B' AS Message
                UNION
                    SELECT 'C' AS Message
                UNION
                    SELECT 'D' AS Message
)

SELECT @msg = COALESCE(ISNULL(@msg,'Attachements') + ', ','') +  Message FROM CTE

SELECT @msg + ' are missing.'

它正在生成输出:-

Attachments, A, B, C, D are missing.

如何避免单词 "Attachments"后面的第一个逗号？请帮忙。

也欢迎其他满足要求的技术。

谢谢。

Answer 1

而不是使用 undocumented/unsupported way of concatenating strings ，请改用 FOR XML PATH:

DECLARE @msg NVARCHAR(MAX) = NULL

;WITH CTE AS (
    SELECT 'A' AS Message
    UNION
    SELECT 'B' AS Message
    UNION
    SELECT 'C' AS Message
    UNION
    SELECT 'D' AS Message
)

SELECT @msg = 'Attachments ' + 
    STUFF((
        SELECT ', ' + Message 
        FROM CTE
        ORDER BY Message
        FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)')
    , 1, 2, ' ')

SELECT @msg + ' are missing.'