即使字段为空， stuff() 也会添加分隔符 [英] stuff() adds separator even when fields are empty

问题描述

请考虑以下声明:

select stuff((
           select '; ' + ([FIELD_1] + [FIELD_2] + [...] + [FIELD_N])
           from   [TABLE] t1
           where  t1.[ID] = t2.[ID]
           for    xml path ('')
        ),1,1, '')
from    [TABLE] t2

如果该语句连接了 10 个具有相同 ID 的记录，但所有记录都没有值(''，或为空，而不是 null)，则输出为:

If the statement concatenates 10 records with the same ID but all records have no value ('', or empty, instead of null), the output is:

; ; ; ; ; ; ; ; ; ;

如果填满了2条记录，我以

If 2 records are filled, I end up with

; ; ; AAA; ; ; ; BBB;

在这两种情况下，我想要的是 null，分别是 AAA;BBB

What I would want in these 2 cases is null, respectively AAA; BBB

我试着像这样修复它:

select stuff((
           select case when [FIELD_1] <> '' then '; ' + ([FIELD_1]) else '' end
           from   [TABLE] t1
           where  t1.[ID] = t2.[ID]
           for    xml path ('')
        ),1,1, '')
from    [TABLE] t2

这很有效，当我只选择 FIELD_1 时，这对我来说已经足够优雅了.但是当我选择许多连接的字段 (FIELD_1 + ... + FIELD_N) 时，这会变得非常难看.

This works, and is elegantly enough for me when I'm only selecting FIELD_1. But when I'm selecting many concatinated fields (FIELD_1 + ... + FIELD_N), this becomes quite ugly fast.

我做错了什么?stuff() 不是应该把我的问题当作一个函数来处理吗?

What am I doing wrong? Isn't stuff() supposed to take care of my problem as a function?

推荐答案

在 WHERE 子句中添加条件以排除所有值为空字符串的行

add a condition to the WHERE clause to exclude rows with all value empty string

select stuff((
           select '; ' + ([FIELD_1] + [FIELD_2] + [...] + [FIELD_N])
           from   [TABLE] t1
           where  t1.[ID] = t2.[ID]
           and    [FIELD_1] + [FIELD_2] + [...] + [FIELD_N] <> ''
           for    xml path ('')
        ),1,1, '')
from    [TABLE] t2

这篇关于即使字段为空， stuff() 也会添加分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！