SQL: объединить значения столбцов в одну строку в строку, разделенную запятой

Скажем, у меня есть таблица вроде этого в 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

Ответ 1

Я думаю, что это позаботится обо всех проблемах, которые я обнаружил в других ответах. Нет необходимости проверять длину вывода или проверять, является ли главный символ запятой, не беспокоиться о конкатенации нестроковых типов, без значительного увеличения сложности, когда неизбежно добавляются другие столбцы (например, Почтовый код)...

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, но здесь это не полезно, так как вам по-прежнему необходимо включать запятые между открытыми значениями, и нет возможности сделать это - он просто путает значения вместе без опции для разделителя. Это позволяет избежать необходимости беспокоиться о типах, отличных от строки, но не позволяет очень легко обрабатывать нули и ненулевые значения.

Ответ 2

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

Ответ 3

Это сложная проблема, потому что запятые должны проходить между:

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

похоже, что он должен работать, но мы можем получить постороннюю запятую в конце, например, когда страна NULL. Таким образом, это должно быть немного сложнее:

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

Без большой промежуточной логики я думаю, что самый простой способ - добавить запятую к каждому элементу, а затем удалить любую постороннюю запятую в конце.

Ответ 4

Используйте оператор "+".

Понимать, что нулевые значения не работают с оператором '+' (например, "Winnepeg" + null = null), поэтому не забудьте использовать функции ISNULL() или COALESCE() для замены нулей пустая строка, например: ISNULL ('Winnepeg', '') + ISNULL (null, '').

Кроме того, если даже возможно, что одна из ваших столбцов может быть интерпретирована как число, тогда обязательно используйте функцию CAST(), чтобы избежать ошибок, например: CAST ('Winnepeg' как varchar (100)).

Большинство примеров до сих пор игнорируют одну или несколько частей этого. Кроме того, в некоторых примерах используются подзапросы или выполняется проверка длины, что вам действительно не нужно делать - просто не обязательно, хотя ваш оптимизатор может сэкономить вас в любом случае, если вы это сделаете.

Удача

Ответ 5

уродливый, но он будет работать для 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

Ответ 6

Вот вариант:

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