Объединение строк по пути XML

Question

Привет! Сегодня я узнал for xml path Техника объединения строк в MSSQL. Так как я никогда не работал с xml в mssql и google не помог, я должен спросить вас.

Давайте представим случай по умолчанию. Нам нужно объединить некоторые строки из таблицы:

declare @xmlRepNames xml = (
    select          
        ', [' + report_name + ']'
    from (
        select distinct
            report_order,
            report_name
        from #report
    ) x
    order by
        report_order
    for xml path(''), type)

select
    stuff((select @xmlRepNames.value('.', 'nvarchar(max)')), 1, 1, '')

Итак, я получаю что-то вроде этого:

[str1], [str2], [strn]

Хорошо. Работает нормально. Но у меня есть два очень похожих сцепленных блока. Разница только в том, как выглядит строка результата:

• [str1], [str2], [strn]
  а также
• isnull([str1], 0) as [str1], isnull([str2], 0) as [str2], isnull([strn], 0) as [strn]

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

declare @xmlRepNames xml = (
    select
        ', [' + report_name + ']' as name,
        ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res
    from (
        select distinct
            report_order,
            report_name
        from #report
    ) x
    order by
        report_order
    for xml path(''), type)

select
    stuff((select @xmlRepNames.value('/name', 'nvarchar(max)')), 1, 1, ''),
    stuff((select @xmlRepNames.value('/res', 'nvarchar(max)')), 1, 1, '')

но это поднимает ошибку "XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'",
Для замены, например, '/name' в '/name[1]' или любой другой '/name[x]', вернет только x-ю запись 'name', но не все записи 'name' объединенные.
[вопрос]: возможно ли решить проблему вторым способом, как я хочу, и если это возможно, то как?
[Отказ от ответственности]: проблема для меня сейчас не очень серьезна (1-й способ немного более уродливый, но тоже неплохой), но мне кажется очень интересным, как прийти:) Спасибо!

Answer 1

Ваш подзапрос не может возвращать два значения. Если вы просто хотите объединить строки, вам не нужно xml Тип данных на всех. Вы можете сделать stuff() и подзапрос в одном выражении:

declare @Rep1Names nvarchar(max) = (
    stuff((select ', [' + report_name + ']' as name
           from (select distinct report_order, report_name
                 from #report
                ) x
           order by report_order
           for xml path('')
          )
         ), 1, 1, '');

declare @Rep2Names nvarchar(max) = (
    stuff(select ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res
           from (select distinct report_order, report_name
                 from #report
                ) x
           order by report_order
           for xml path('')
          )
   ), 1, 1, '');

Answer 2

Итак, я не был полностью удовлетворен тем, как предложил Гордон Линофф, и, поскольку я нашел такую ​​проблему актуальной для меня, я добавляю здесь другое решение без использования for xml path:

declare
    @pivot_sequence nvarchar(max),
    @columns_sequence nvarchar(max)

select
    @pivot_sequence = coalesce(@pivot_sequence + ', [', '[') 
        + col_name + ']',
    @columns_sequence = coalesce(@columns_sequence + ', ', '')
        + 'isnull([' + col_name + '], 0) as [' + col_name + ']'
from some_table
order by
    /* some_columns if needed to order concatenation */

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