SQL:将单行中的列值连接成以逗号分隔的字符串 [英] SQL: Concatenate column values in a single row into a string separated by comma

问题描述

假设我在 SQL Server 中有一个这样的表:

Let's say I have a table like this in 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:

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

推荐答案

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

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 添加了一个新的 T-SQL 函数，名为 CONCAT，但它在这里没有用，因为您仍然必须有选择地在发现的值之间包含逗号，并且没有办法做到这一点 - 它只是将值组合在一起，没有分隔符的选项.这避免了不必担心非字符串类型，但不允许您非常优雅地处理空值与非空值.

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:将单行中的列值连接成以逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！