如何从SQL生成的逗号分隔列表中排除指定值MYVALUE2？

根据你的需求，要在生成的逗号分隔列表中排除等于MYVALUE2的项，分两种场景给出修改方案：

场景1：排除拼接后的整个项等于MYVALUE2 #

如果是最终列表里的完整条目（比如SID0000140 - Serie vom Milchtraum这类格式的内容）等于MYVALUE2时需要排除，直接在WHERE条件里增加对拼接结果的过滤即可：

DECLARE @listStr VARCHAR(MAX)

SELECT 
    @listStr = COALESCE (@listStr + ',', '') + Name + ' - ' + REPLACE(Description, ',', '')
FROM 
    admin.Jobs
WHERE 
    Customer = 'MYVALUE1'
    AND Category = 'SERIEN'
    -- 过滤掉拼接后等于MYVALUE2的项
    AND (Name + ' - ' + REPLACE(Description, ',', '')) <> 'MYVALUE2'
ORDER BY 
    Name

SELECT @listStr

场景2：排除某个字段等于MYVALUE2的记录 #

如果是Name或Description字段本身等于MYVALUE2时需要排除对应的条目，直接在WHERE里添加对应字段的过滤条件：

排除Name等于MYVALUE2的记录 #

DECLARE @listStr VARCHAR(MAX)

SELECT 
    @listStr = COALESCE (@listStr + ',', '') + Name + ' - ' + REPLACE(Description, ',', '')
FROM 
    admin.Jobs
WHERE 
    Customer = 'MYVALUE1'
    AND Category = 'SERIEN'
    AND Name <> 'MYVALUE2'
ORDER BY 
    Name

SELECT @listStr

排除Description等于MYVALUE2的记录 #

DECLARE @listStr VARCHAR(MAX)

SELECT 
    @listStr = COALESCE (@listStr + ',', '') + Name + ' - ' + REPLACE(Description, ',', '')
FROM 
    admin.Jobs
WHERE 
    Customer = 'MYVALUE1'
    AND Category = 'SERIEN'
    AND Description <> 'MYVALUE2'
ORDER BY 
    Name

SELECT @listStr

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