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

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.