SQL:将单个行中的列值连接到用逗号分隔的字符串。

Let's say I have a table like this in SQL Server:

假设我在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 

How can I get a query result so that the location is a concatenation of the City, Province, and Country separated by ", ", with nulls omitted. I'd like to ensure that there aren't any trailing comma, preceding commas, or empty strings. For example:

如何获得查询结果，以便该位置是由“，”分隔的城市、省和国家的连接，并省略了null。我希望确保不存在尾逗号、前逗号或空字符串。例如:

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

6 个解决方案

#1

I think this takes care of all of the issues I spotted in other answers. No need to test the length of the output or check if the leading character is a comma, no worry about concatenating non-string types, no significant increase in complexity when other columns (e.g. Postal Code) are inevitably added...

我认为这可以解决我在其他答案中发现的所有问题。不需要测试输出的长度，也不需要检查主导字符是否是逗号，不需要担心连接非字符串类型，也不需要在不可避免地添加其他列(如邮政编码)时显著增加复杂性……

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 added a new T-SQL function called CONCAT, but it is not useful here, since you still have to optionally include commas between discovered values, and there is no facility to do that - it just munges values together with no option for a separator. This avoids having to worry about non-string types, but doesn't allow you to handle nulls vs. non-nulls very elegantly.

SQL Server 2012添加了一个名为CONCAT的新的T-SQL函数，但是在这里没有用处，因为您仍然需要在已发现的值之间包含逗号，而且没有这种功能——它只是将值合并在一起，没有选择分隔符。这避免了对非字符串类型的担心，但是不允许您非常优雅地处理null和非空值。

#2

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

#3

This is a hard problem, because the commas have to go in-between:

这是一个困难的问题，因为逗号之间必须有:

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

seems like it should work, but we can get an extraneous comma at the end, such as when country is NULL. So, it needs to be a bit more complicated:

看起来应该是可行的，但是我们可以在最后得到一个额外的逗号，比如当国家为空时。所以，它需要更复杂一点:

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

Without a lot of intermediate logic, I think the simplest way is to add a comma to each element, and then remove any extraneous comma at the end.

没有很多中间逻辑，我认为最简单的方法是在每个元素中添加一个逗号，然后在末尾删除任何无关的逗号。

#4

Use the '+' operator.

使用“+”运算符。

Understand that null values don't work with the '+' operator (so for example: 'Winnepeg' + null = null), so be sure to use the ISNULL() or COALESCE() functions to replace nulls with an empty string, e.g.: ISNULL('Winnepeg','') + ISNULL(null,'').

理解null值不能与'+'操作符一起工作(例如:'Winnepeg' + null = null)，所以一定要使用ISNULL()或合并函数来替换空字符串，例如:ISNULL('Winnepeg'，') + ISNULL(null， ")。

Also, if it is even remotely possible that one of your collumns could be interpreted as a number, then be sure to use the CAST() function as well, in order to avoid error returns, e.g.: CAST('Winnepeg' as varchar(100)).

此外，如果您的一个排序规则可以被解释为数字，那么一定要使用CAST()函数，以避免错误返回，例如:CAST('Winnepeg' as varchar(100)))。

Most of the examples so far neglect one or more pieces of this. Also -- some of the examples use subqueries or do a length check, which you really ought not to do -- just not necessary -- though your optimizer might save you anyway if you do.

到目前为止，大多数示例都忽略了其中的一个或多个部分。此外——有些示例使用子查询或做长度检查，这是您确实不应该做的——只是没有必要——尽管如果这样做，优化器可能会保存您。

Good Luck

祝你好运

#5

ugly but it will work for MS SQL:

虽然很丑，但对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

#6

Here is an option:

这是一个选择:

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