Склеивание значений столбцов – не самая тривиальная задача в T-SQL, если вы не знакомы со стандартом ANSI. Согласно ему, при сложении хотя бы одного составляющего со значением NULL результат будет также NULL. Об этом знают администраторы баз данных, но не факт, что известно обычным разработчикам.

Рассмотрим несколько особенностей склеивания столбцов с из таблицы MS SQL Server. Для примера будем использовать бесплатную учебную базу данных Northwind и таблицу Customers.

Запустим SQL-запрос и посмотрим на результат (Таблица 1):

 select Address, City, Region, PostalCode, Country from Customers

Таблица 1. Результат SQL запроса

Address	City	Region	PostalCode	Country
Obere Str. 57	Berlin	NULL	12209	Germany
Avda. de la Constitución 2222	México D.F.	NULL	5021	Mexico
Mataderos  2312	México D.F.	NULL	5023	Mexico
120 Hanover Sq.	London	NULL	WA1 1DP	UK
Berguvsvägen  8	Luleå	NULL	S-958 22	Sweden
Forsterstr. 57	Mannheim	NULL	68306	Germany
24, place Kléber	Strasbourg	NULL	67000	France

 

В таблице видно, что некоторые значения столбца Region имеют значение NULL.

Склеим строки так, чтобы полный адрес был единственным значением:

select Country + ', ' +  City + ', ' + Region + ', ' + PostalCode + ', ' + Address from Customers

Результат будет отличен от того, каким его ожидает увидеть разработчик. Логично было б увидеть что-то типа "Germany, Berlin, , 12209, Obere Str. 57", но результатом будет много значений NULL  в строках (Рисунок 1):

Можно интерпретировать значение NULL как пустую строку с помощью параметра CONCAT_NULL_YIELDS_NULL. По умолчанию его значение ON, но можно его поменять для текущей сессии на OFF. Тогда результат будет больше похож на то, что нужно.

SET CONCAT_NULL_YIELDS_NULL OFF
select Country + ', ' +  City + ', ' + Region + ', ' + PostalCode + ', ' + Address from Customers

 

 

Не забудьте снова вернуть значение параметра CONCAT_NULL_YIELDS_NULL на ON (SET CONCAT_NULL_YIELDS_NULL ON)

 

Другой способ – воспользоваться функцией COALESCE (expression, value_instead_of_null), которая возвращает значение "value_instead_of_null", если выражение есть NULL. Запрос для склеивани строк будет таким:

select COALESCE(Country, '') + ', ' +  COALESCE(City, '') + ', ' + COALESCE(Region, '') + ', ' + COALESCE(PostalCode, '') + ', ' + COALESCE(Address, '') from Customers

Результат SQL-запроса представлен на Рисунке 2

 

В версии MS SQL Server 2012 появилась функция CONCAT (expr1, expr2, expr3, … , exprN) для склеивания строчек.

select CONCAT(Country, ', ', City, ', ', Region, ', ', PostalCode,', ', Address ) from Customers

 

Результат этого SQL-запроса такой же, как и на Рисунке 2.

 

Строчки склеиваются, несмотря на значения NULL. Но для финальной красоты остается убрать двойные запятые ", ,". Это можно сделать с помощью функции REPLACE:

select REPLACE( COALESCE(Country, '') + ', ' +  COALESCE(City, '') + 
			', ' + COALESCE(Region, '') + ', ' + COALESCE(PostalCode, '') +
			', ' + COALESCE(Address, '') , ', ,', ',') 
			from Customers