Объединение данных SQL Server: GROUP BY и строка с email

Быстрый ответ

В SQL Server вы можете преобразовать группу строк в одну, используя встроенные функции STUFF и конструкцию FOR XML PATH. Рассмотрим пример, который объединяет названия продуктов в каждом заказе:

SELECT 
  o.OrderId, 
  STUFF((SELECT ', ' + i.ProductName 
         FROM Products i
         WHERE i.OrderId = o.OrderId
         FOR XML PATH('')), 1, 2, '') AS ProductNames
FROM 
  Orders o
GROUP BY 
  o.OrderId;

Этот подход эффективен и не требует использования вспомогательных разделителей.

Расширенные методы агрегации

Использование CROSS APPLY для динамической группировки

CROSS APPLY — это мощный инструмент для формирования коррелирующих подзапросов в SQL Server. В сочетании с FOR XML PATH, он позволяет формировать строки с разделителями:

SELECT 
  o.OrderId, 
  ca.ProductNames
FROM 
  Orders o
CROSS APPLY
  (SELECT STUFF((SELECT ', ' + i.ProductName 
                  FROM Products i
                  WHERE i.OrderId = o.OrderId
                  FOR XML PATH('')), 1, 2, '') AS ProductNames) ca
GROUP BY 
  o.OrderId, ca.ProductNames;

Подробнее об этом расскажет наш спикер на видео

Избегание дубликатов: применение DISTINCT

Чтобы исключить повторение значений в результирующем списке, используйте ключевое слово DISTINCT:

SELECT 
  o.OrderId, 
  STUFF((SELECT DISTINCT ', ' + i.ProductName 
         FROM Products i
         WHERE i.OrderId = o.OrderId
         FOR XML PATH('')), 1, 2, '') AS UniqueProductNames
FROM 
  Orders o
GROUP BY 
  o.OrderId;

Очистка результата: удаление "лишних" запятых с помощью SUBSTRING

Если результат вашего запроса оканчивается нежелательной запятой, используйте функцию SUBSTRING:

SELECT 
  o.OrderId, 
  SUBSTRING((SELECT ', ' + i.ProductName 
             FROM Products i
             WHERE i.OrderId = o.OrderId
             FOR XML PATH('')), 3, 1000) AS ProductNames
FROM 
  Orders o
GROUP BY 
  o.OrderId;

Визуализация

Возьмем для примера книгу рецептов, где каждый рецепт предполагает использование своего набора ингредиентов:

Рецепты       | Ингредиенты
------------- | ----------------
Яблочный пирог| Яблоко, Сахар, Мука
Фруктовый салат| Яблоко, Банан, Апельсин
Ягодный тарт  | Черника, Сахар, Мука

Применение GROUP BY позволяет генерировать списки ингредиентов, разделенные запятыми:

SELECT Recipes,
       STRING_AGG(Ingredients, ', ') WITHIN GROUP (ORDER BY Ingredients) AS Ingredients_List
FROM Recipe_Book
GROUP BY Recipes;

В результате получаем краткий список ингредиентов для каждого рецепта:

Яблочный пирог:    🍏, 🍬, 🌾
Фруктовый салат:  🍏, 🍌, 🍊
Ягодный тарт:     🫐, 🍬, 🌾

Условные конструкции: группировка с особенностями

Если требуется отсортировать данные по одному критерию и дополнительно разделить их по другому, применяются условные конструкции CASE или IIF:

SELECT 
  o.OrderId, 
  STUFF((SELECT ', ' + CASE WHEN i.IsSpecial = 1 THEN 'Особый: ' ELSE '' END + i.ProductName 
         FROM Products i
         WHERE i.OrderId = o.OrderId
         FOR XML PATH('')), 1, 2, '') AS ProductNames
FROM 
  Orders o
GROUP BY 
  o.OrderId;

Современные подходы: STRING_AGG в SQL Server 2017 и более новых версиях

С появлением SQL Server 2017 функция STRING_AGG стала значительно упрощать сбор строк:

SELECT 
  o.OrderId, 
  STRING_AGG(i.ProductName, ', ') WITHIN GROUP (ORDER BY i.ProductName) AS ProductNames
FROM 
  Orders o
JOIN 
  Products i ON o.OrderId = i.OrderId
GROUP BY 
  o.OrderId;

Демонстрация на примере SQL Fiddle

Вы можете практически попробовать эти техники на платформе SQL Fiddle. Здесь вы можете проверить код на практике, внести изменения и увидеть результаты в реальном времени.

Полезные материалы

1. STRING_AGG (Transact-SQL) – SQL Server | Microsoft Learn — официальная документация по использованию STRING_AGG в SQL Server.
2. Concatenating Row Values in Transact-SQL – Simple Talk — обзор альтернатив STRING_AGG, в котором демонстрируется использование FOR XML PATH и STUFF.
3. Multiple options to transposing rows into columns — обсуждаются различные способы преобразования данных с использованием GROUP BY и CASE.
4. COALESCE (Transact-SQL) – SQL Server | Microsoft Learn — пособие Microsoft по функции COALESCE для работы с NULL в групповых конкатенациях.
5. TSQL – Concatenate Rows using FOR XML PATH() — детализированное руководство по слиянию строк с помощью FOR XML PATH.
6. SQL COUNT(), AVG() and SUM() Functions — ресурс, охватывающий ключевые агрегатные функции SQL, используемые в комбинации с GROUP BY.