Функция SQL Server STRING_AGG

В этом руководстве вы узнаете, как использовать функцию SQL Server STRING_AGG() для объединения строк в одну строку с указанным разделителем.

Введение в функцию SQL Server STRING_AGG()

STRING_AGG() — это агрегатная функция, которая объединяет строки строк в одну строку, разделенную указанным разделителем. Она не добавляет разделитель в конец результирующей строки.

Вот синтаксис функции STRING_AGG():

STRING_AGG( expression, separator ) [ order_clause ]

В этом синтаксисе:

• выражение — это значение любого типа, которое может быть преобразовано в VARCHAR и NVARCHAR.
• разделитель — разделитель для строки результата. Может быть литералом или переменной.
• order_clause указывает порядок сортировки объединенных результатов с использованием предложения WITHIN GROUP.

Вот синтаксис order_clause:

WITHIN GROUP( ORDER BY expression [ ASC | DESC ] )

Функция STRING_AGG() игнорирует NULL и не добавляет разделитель для NULL при выполнении конкатенации.

Примеры функции SQL Server STRING_AGG()

Для демонстрации мы будем использовать таблицу sales.customers из примера базы данных :

1) Использование функции STRING_AGG() для создания списка адресов электронной почты

В этом примере функция STRING_AGG() используется для генерации списков адресов электронной почты клиентов по городу:

SELECT
    city, 
    STRING_AGG(email,';') email_list
FROM
    sales.customers
GROUP BY
    city;

Вот частичный набор результатов:

Для сортировки списка адресов электронной почты используйте предложение WITHIN GROUP:

SELECT
    city, 
    STRING_AGG(email,';') 
        WITHIN GROUP(ORDER BY email) email_list
FROM
    sales.customers
GROUP BY
    city;

На следующем рисунке показан частичный вывод:

2) Использование функции STRING_AGG() для создания списка имен клиентов, разделенных на новые строки.

В следующем примере функция STRING_AGG() используется для создания списка имен клиентов в штате CA, разделенных символами новой строки:

SELECT
  STRING_AGG(CONCAT_WS(' ', first_name, last_name), CHAR(13)) customer_names
FROM
  sales.customers
WHERE
  state = 'CA';

Выход:

customer_names
--------------
Kasha Todd
Tameka Fisher
Charolette Rice
Corene Wall
Jamaal Albert
...

Если вы используете SQL Server Management Studio(SSMS), необходимо переключить вывод в текстовый формат с помощью сочетания клавиш Ctrl-T для правильного отображения новых строк.

В этом примере мы объединяем имя, пробел и фамилию с помощью функции CONCAT_WS() и создаем одну выходную строку с помощью функции STRING_AGG().

3) Использование функции STRING_AGG() с предложением GROUP BY

Следующий оператор извлекает идентификатор заказа, количество товаров в каждом заказе и список наименований продуктов для соответствующего заказа:

SELECT
  o.order_id,
  COUNT(*) item_count,
  STRING_AGG(p.product_name, ',') WITHIN GROUP(
    ORDER BY
      product_name
  ) product_list
FROM
  sales.orders o
  INNER JOIN sales.order_items i ON i.order_id = o.order_id
  INNER JOIN production.products p ON p.product_id = i.product_id
GROUP BY
  o.order_id;

В следующем примере показан частичный вывод:

Как это работает.

• Объедините таблицы orders, order_items и products с помощью предложений INNER JOIN.
• Группируйте заказы по идентификатору заказа с помощью предложения GROUP BY.
• Получите количество товаров в заказе с помощью функции COUNT(*).
• Объедините названия продуктов заказа с помощью функции STRING_AGG().

Краткое содержание

• Используйте функцию STRING_AGG() для объединения строк в одну строку с указанным разделителем.