如何在SQL中拼接字符串时忽略空字段，避免多余分隔符？

需求：将最多6个字段（myfield1至myfield6）用分号加空格（; ）拼接为单个字符串，若字段为空则不添加对应分隔符。

示例数据表 #

现有SQL语句 #

select 
concat(case when my_field1 = '-1' then 'Cond1; ' end, case when my_field2 = '-1' then 'Cond2; ' end, case when my_field3 = '-1' then 'Cond3; ' end, case when my_field4 = '-1' then 'Cond4; ' end, case when my_field5 = '-1' then 'Cond5; ' end, case when my_field16 = '-1' then 'Cond6' end)
as "example"
from table

当前结果 #

Cond1; Cond2; Cond3; Cond4; Cond5; Cond6
Cond1; ; ; Cond4; Cond5; Cond6
Cond1; Cond2; Cond3; ; Cond5; Cond6

期望结果 #

Cond1; Cond2; Cond3; Cond4; Cond5; Cond6
Cond1; Cond4; Cond5; Cond6
Cond1; Cond2; Cond3; Cond5; Cond6

方法1：用concat_ws（推荐，适配MySQL、PostgreSQL等多数数据库） #

concat_ws的特性是自动忽略空值，仅用指定分隔符拼接非空参数，完美匹配需求。同时要修正原SQL里的字段名笔误（my_field1改为myfield1，my_field16改为myfield6）：

select
concat_ws('; ',
    case when myfield1 = '-1' then 'Cond1' end,
    case when myfield2 = '-1' then 'Cond2' end,
    case when myfield3 = '-1' then 'Cond3' end,
    case when myfield4 = '-1' then 'Cond4' end,
    case when myfield5 = '-1' then 'Cond5' end,
    case when myfield6 = '-1' then 'Cond6' end
) as "example"
from table

方法2：用string_agg（适配PostgreSQL、SQL Server 2017+） #

先把符合条件的Cond值单独提取，再用分号加空格聚合，自动跳过空值：

select
string_agg(cond, '; ') as "example"
from (
    select id, case when myfield1 = '-1' then 'Cond1' end as cond from table
    union all
    select id, case when myfield2 = '-1' then 'Cond2' end from table
    union all
    select id, case when myfield3 = '-1' then 'Cond3' end from table
    union all
    select id, case when myfield4 = '-1' then 'Cond4' end from table
    union all
    select id, case when myfield5 = '-1' then 'Cond5' end from table
    union all
    select id, case when myfield6 = '-1' then 'Cond6' end from table
) t
where cond is not null
group by id

方法3：兼容无聚合函数的老版本数据库 #

先拼接所有带分隔符的有效Cond，再去掉末尾多余的分隔符：

select
trim(trailing '; ' from concat(
    case when myfield1 = '-1' then 'Cond1; ' end,
    case when myfield2 = '-1' then 'Cond2; ' end,
    case when myfield3 = '-1' then 'Cond3; ' end,
    case when myfield4 = '-1' then 'Cond4; ' end,
    case when myfield5 = '-1' then 'Cond5; ' end,
    case when myfield6 = '-1' then 'Cond6; ' end
)) as "example"
from table

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