Блог

Я использую for xml path для агрегирования значений:

 select max(x.Ids), max(x.Number), (select country,city for json path) as [Json]
from t
outer apply (
    select Stuff((select ','   Convert(varchar(10),t2.Id)
    from t t2
    where t2.city=t.city and t2.country=t.country
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Ids,
    Stuff((select ','   Convert(varchar(10),t2.Number)
    from t t2
    where t2.city=t.city and t2.country=t.country
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Numbers
)x
 

Варианты выбора/запросы внутри outer apply идентичны. Интересно, можно ли повторно использовать этот запрос? Я попытался создать CTE внутри outer apply , но это, похоже, не принимается SQL Server:

 select max(x.Ids), max(x.Number), (select country,city for json path) as [Json]
from t
outer apply (
    with Test_CTE( Id, Number) AS (
        SELECT ID, Number FROM t t2
        where t2.city=t.city and t2.country=t.country
    )
    select Stuff((select ','   Convert(varchar(10),t2.Id)
    from Test_CTE t2        
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Ids,
    Stuff((select ','   Convert(varchar(10),t2.Number)
    from Test_CTE t2 
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Numbers
)x
 

Приведенная выше попытка приводит к следующей ошибке:

Неправильный синтаксис рядом с ключевым словом «с».

В свой for xml вы можете включить оба столбца , избежать объединения с», » и сохранить имена столбцов, чтобы выходные данные содержали значения, заключенные в элементы.

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

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

Итак, для таких данных, как это:

 declare @t table (Id int, Number int, country char(1), city char(1));
insert @t values 
    (1, 101, 'a', 'z'),
    (2, 102, 'b', 'y'),
    (3, 103, 'a', 'z');
 

Вы можете это сделать:

 select      [Json] = (select t.country, t.city for json path),
        
            Ids = stuff(replace(replace(convert(varchar(max),
                ap._xml.query('./Id')
            ),'<Id>', ','),'</Id>',''), 1, 1, ''),
        
            Nums = stuff(replace(replace(convert(varchar(max),
                ap._xml.query('./Number')
            ),'<Number>', ','),'</Number>',''), 1, 1, '')

from        (select distinct t.city, t.country from @t t) t
cross apply (select _xml = (
                select  t2.Id, 
                        t2.Number
                from    @t t2
                where   t2.city = t.city and t2.country = t.country 
                for     xml path(''), type
            )) ap;
 

И получите такой результат:

Но я не знаю, считаете ли вы, что новый подход более элегантен, чем оригинальный. Я бы сказал, что это так, но все равно довольно уродливо. Имейте в виду, что в более новых версиях sql server есть string_agg функция, которая делает все это за вас.