5 cпособов осуществить агрегацию строк в MS SQL

Иногда возникает необходимость осуществить агрегацию строк в SQL запросе, то есть, по такому набору данных:

GroupId	Item
1	AAA
2	IS
5	OMG
2	WHAT
2	THE
1	This

получить примерно такой:

GroupId	ItemList
1	AAA,This
2	IS,WHAT,THE
5	OMG

MySQL, например, для таких целей обладает встроенной функцией GROUP_CONCAT():

SELECT GroupId, GROUP_CONCAT(Item SEPARATOR ",") AS ItemList
FROM Items

В MS SQL Server'e такой функции нету, поэтому приходится извращаться. Перед тем, как приступить, сделаем скрипт для создания тестовой таблицы:

CREATE TABLE Items(GroupId INT, Item NVARCHAR(10))

INSERT INTO Items(GroupId, Item)
SELECT 1 AS GroupId, 'AAA' AS Item
  UNION ALL
SELECT 2, 'IS'
  UNION ALL
SELECT 5, 'OMG'
  UNION ALL
SELECT 2, 'WHAT'
  UNION ALL
SELECT 2, 'THE'
  UNION ALL
SELECT 1, 'This'

Итак, начнем.

Самый тупой прямолинейный способ — создать временную таблицу и собирать в нее промежуточные результаты агрегации, пробегая по таблице Items курсором. Этот способ очень медленно работает и код его страшен. Любуйтесь:

DECLARE @Aggregated TABLE (GroupId INT, ItemList NVARCHAR(100))

DECLARE ItemsCursor CURSOR READ_ONLY
FOR SELECT GroupId, Item
    FROM Items

DECLARE @CurrentGroupId INT
DECLARE @CurrentItem NVARCHAR(10)
DECLARE @CurrentItemList NVARCHAR(100)

OPEN ItemsCursor

FETCH NEXT FROM ItemsCursor
INTO @CurrentGroupId, @CurrentItem

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentItemList = (SELECT ItemList
                            FROM @Aggregated
                            WHERE GroupId = @CurrentGroupId)

    IF @CurrentItemList IS NULL
        INSERT INTO @Aggregated(GroupId, ItemList)
        VALUES(@CurrentGroupId, @CurrentItem)
    ELSE
        UPDATE @Aggregated
        SET ItemList = ItemList + ',' + @CurrentItem
        WHERE GroupId = @CurrentGroupId

    FETCH NEXT FROM ItemsCursor
    INTO @CurrentGroupId, @CurrentItem
END

CLOSE ItemsCursor
DEALLOCATE ItemsCursor

SELECT GroupId, ItemList
FROM @Aggregated

Есть более красивый способ, не использующий временных таблиц. Он основан на трюке SELECT var = var + ',' + col FROM smwhere. Да, так можно и это работает:

CREATE FUNCTION ConcatItems(@GroupId INT)
   RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @ItemList varchar(8000)
    SET @ItemList = ''

    SELECT @ItemList = @ItemList + ',' + Item
    FROM Items
    WHERE GroupId = @GroupId

    RETURN SUBSTRING(@ItemList, 2, 100)
END

GO

SELECT GroupId, dbo.ConcatItems(GroupId) ItemList
FROM Items
GROUP BY GroupId

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

SELECT GroupId,
       CASE Item2 WHEN '' THEN Item1
           ELSE CASE Item3 WHEN '' THEN Item1 + ',' + Item2
           ELSE CASE Item4 WHEN '' THEN Item1 + ',' + Item2 + ',' + Item3
           ELSE Item1 + ',' + Item2 + ',' + Item3 + ',' + Item4
       END END END AS ItemList
FROM (
  SELECT GroupId,
       MAX(CASE ItemNo WHEN 1 THEN Item ELSE '' END) AS Item1,
       MAX(CASE ItemNo WHEN 2 THEN Item ELSE '' END) AS Item2,
       MAX(CASE ItemNo WHEN 3 THEN Item ELSE '' END) AS Item3,
       MAX(CASE ItemNo WHEN 4 THEN Item ELSE '' END) AS Item4
  FROM (
    SELECT GroupId,
         Item,
         ROW_NUMBER() OVER (PARTITION BY GroupId ORDER BY Item) ItemNo
    FROM Items
  ) AS OrderedItems
  GROUP BY GroupId
) AS AlmostAggregated

Да, много кода. Но зато ни одного лишнего объекта в БД — просто один чистый селект. Это иногда важно.

Тем не менее, существует способ обойти и ограничение на размер группы, оставшись при этом в рамках одного запроса. Мы будем собирать все элементы группы в XML-поле, которое затем сконвертим к строковому типу и заменим теги между элементами на запятые:

SELECT GroupId,
       REPLACE(SUBSTRING(ItemListWithTags, 4, LEN(ItemListWithTags)-7),
               '<a>',
               ',') AS ItemList
FROM (
  SELECT GroupId,
       CAST(XmlItemList AS NVARCHAR(200)) ItemListWithTags
  FROM (
    SELECT GroupId,
         (SELECT Item AS A
        FROM Items ii
        WHERE ii.GroupId = GroupIds.GroupId
        FOR XML PATH('')) AS XmlItemList
    FROM (SELECT DISTINCT GroupId FROM Items) AS GroupIds
  ) AS subq1
) AS subq2

В общем, работает не очень шустро, зато всегда. И, конечно, нужен SQL Server не ниже 2000.

Да, еще есть способ агрегировать строки через CLR Aggregate Functions, но это вообще мрачный ужас, ибо вусмерть медленно и несообразно сложности задачи. Если возникнет достаточный спрос на такую статью, напишу её позже.

Жду с нетерпением комментариев и критики. И ещё: если кто-нибудь знает, как можно сделать такую подсветку кода, как я сделал у себя дома — подскажите. Я, кроме как вставить скриншоты, другого способа пока не вижу.