SQL：将单个行中的列值连接成由逗号分隔的字符串

Question

SQL：将单个行中的列值连接成由逗号分隔的字符串

20

假设我在 SQL Server 中有这样一张表：

Id    City           Province             Country
1     Vancouver      British Columbia     Canada
2     New York       null                 null
3     null           Adama                null
4     null           null                 France
5     Winnepeg       Manitoba             null
6     null           Quebec               Canada
7     Seattle        null                 USA

我该如何获取查询结果，使得位置是城市、省份和国家以", "分隔的连接，同时省略空值。我希望确保不会有尾随逗号、前导逗号或空字符串出现。例如:

Id    Location
1     Vancouver, British Columbia, Canada
2     New York
3     Adama
4     France
5     Winnepeg, Manitoba
6     Quebec, Canada
7     Seattle, USA

- Johnny Oshika

糟糕...我删掉了错误的答案...没有仔细阅读。 - Smandoli

@AaronBertrand，你能告诉我重复的问题在哪里吗？我在SO上花了很多时间寻找答案，但没有找到任何有用的信息。 - Johnny Oshika

@SQLCurious，我尝试了很多方法，但惊讶地发现这比我想象的要困难得多。在另一种语言中，我只需使用类似于Array.Join(array, ", ")的东西，但我在SQL中找不到任何类似的东西。您的评论让我相信您认为这很简单，那么您能否提供您的答案？ - Johnny Oshika

这是一个问题链接：https://dev59.com/8m435IYBdhLWcg3wvy2_ - Aaron Bertrand

抱歉 - 我很忙。我以为你可能尝试了一堆情况语句。 - SQLCurious

7个回答

10

select Id ,   
 Coalesce( City + ',' +Province + ',' + Country,
           City+ ',' + Province,
           Province + ',' + Country,
           City+ ',' + Country,
           City,
           Province,
           Country
          ) as location
from table

- Kevin

请注意，为了使其起作用，应启用CONCAT_NULL_YIELDS_NULL。解决问题的不错方法。 - Holger Brandt

11

我并不认为这个设计优雅。如果有5列，9列或者15列怎么办？你真的要把所有可能的组合都列出来吗？天啊。 - Aaron Bertrand

3

我其实同意 @AaronBertrand 的观点。我的解决方案可以解决这个问题，但无法很好地扩展。他在评论中提供的接受答案更为适用。 - Kevin

当列数增加时，这将会导致问题，而且在这里有大约7个检查用于三列组合，如果列数增加了会怎样...... - Pranay Rana

5

这是一个困难的问题，因为逗号必须放在中间：

select id, coalesce(city+', ', '')+coalesce(province+', ', '')+coalesce(country, '')
from t

看起来应该可以工作，但是当国家为空时，可能会在结尾处得到一个多余的逗号。因此，它需要更加复杂：

select id,
       (case when right(val, 2) = ', ' then left(val, len(val) - 1)
             else val
        end) as val
from (select id, coalesce(city+', ', '')+coalesce(province+', ', '')+coalesce(country, '') as val
      from t
     ) t

我认为最简单的方法是给每个元素添加一个逗号，然后删除末尾多余的逗号，这样就不需要太多中间逻辑。

- Gordon Linoff

哎呀，SQL Server 忽略字符串末尾的空格。所以 "-2" 也会移除最后一个字符。我把它改成了 "-1"。 - Gordon Linoff

这仍然过于复杂了（至少在我看来是这样）。你认为这里需要一个子查询还是需要检查尾随字符？ - Aaron Bertrand

对我来说，这个逻辑比你的版本更容易理解，因为逗号自然而然地放在单词后面，而不是前面。除此之外，它们本质上是相同的（实际上我曾考虑过在逗号前面做这件事，但我觉得那样看起来很尴尬）。你的优点是，你不需要知道逗号在哪里才能删除它，因为它总是在开头。 - Gordon Linoff

我认为，仅仅为了在列名之后加上逗号而引入子查询是不必要的复杂性。你的情况可能有所不同。 - Aaron Bertrand

我们其实不必争论。这两个版本本质上都是做同样的事情——用额外的字符构造答案，然后将它们删除。你的版本使用了嵌套函数，我的版本使用了子查询。这主要是审美问题。不过我注意到，你好像喜欢争论。 - Gordon Linoff

显示剩余2条评论

2

使用 '+' 运算符。

请注意，null 值与 '+' 运算符不兼容（例如：'Winnepeg' + null = null），因此请确保使用 ISNULL() 或 COALESCE() 函数将 null 替换为空字符串，例如：ISNULL('Winnepeg','') + ISNULL(null,'')。

另外，如果您的列有可能被解释为数字，则一定要使用 CAST() 函数，以避免错误返回，例如：CAST('Winnepeg' as varchar(100))。

到目前为止，大多数示例都忽略了其中一个或多个部分。此外，有些示例使用子查询或进行长度检查，这是不必要的，尽管如果您这样做，优化器也可能会为您节省时间。

祝你好运。

- Chains

3

+1 这是我唯一能同意的答案，可惜你没有包含任何代码。:-\ - Aaron Bertrand

1

我知道这是一个老问题，但如果今天有人遇到这个问题，SQL Server 2017及更高版本具有STRING_AGG函数，并带有WITHIN GROUP选项:

with level1 as
(select id,city as varcharColumn,1 as columnRanking from mytable
union
select id,province,2 from mytable
union
select id,country,3 from mytable)
select STRING_AGG(varcharColumn,', ')
within group(order by columnRanking)
from level1
group by id

如果除了null之外还存在空字符串，那么应该在第一层使用WHERE子句来排除它们。

- Luc Wanlin

1

虽然不太美观，但对于 MS SQL 来说是可行的：

    select
    id,
    case
        when right(rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,'')),1)=',' then left(rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,'')),LEN(rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,'')))-1)
        else rtrim(coalesce(city + ', ','') + coalesce(province + ', ','') + coalesce(country,''))
    end
from
    table

- Phil

这并没有错，但是它非常复杂。还要考虑当有4、5或6列时，Kevin的答案会变得更加复杂。 - Aaron Bertrand

-2

这里有一个选项：

SELECT (CASE WHEN City IS NULL THEN '' ELSE City + ', ' END) + 
(CASE WHEN Province IS NULL THEN '' ELSE Province + ', ' END) +
(CASE WHEN Country IS NULL THEN '' ELSE Country END) AS LOCATION
FROM MYTABLE

- mreyeros

如果城市为空，你会得到 , 魁北克省，加拿大。我不想要前面的逗号。 - Johnny Oshika

已更新以考虑 NULL 城市。 - mreyeros

仍然存在尾随逗号，例如：温尼伯，曼尼托巴省，。 - Aaron Bertrand

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

我认为这个解决方案处理了其他答案中我发现的所有问题。无需测试输出的长度或检查前导字符是否为逗号，不必担心连接非字符串类型，当不可避免地添加其他列（例如邮政编码）时，复杂度不会显著增加...

DECLARE @x TABLE(Id INT, City VARCHAR(32), Province VARCHAR(32), Country VARCHAR(32));

INSERT @x(Id, City, Province, Country) VALUES
(1,'Vancouver','British Columbia','Canada'),
(2,'New York' , null             , null   ),
(3, null      ,'Adama'           , null   ),
(4, null      , null             ,'France'),
(5,'Winnepeg' ,'Manitoba'        , null   ),
(6, null      ,'Quebec'          ,'Canada'),
(7,'Seattle'  , null             ,'USA'   );

SELECT Id, Location = STUFF(
      COALESCE(', ' + RTRIM(City),     '') 
    + COALESCE(', ' + RTRIM(Province), '') 
    + COALESCE(', ' + RTRIM(Country),  '')
    , 1, 2, '')
  FROM @x;

SQL Server 2012新增了一个名为CONCAT的T-SQL函数，但它在这里是无用的，因为你仍然必须在发现的值之间可选地包含逗号，并且没有设施来执行此操作 - 它只是将值混合在一起而没有选项用于分隔符。这避免了担心非字符串类型，但不能让您非常优雅地处理空值和非空值之间的区别。