Что делает этот запрос для создания списка с разделителями-запятыми SQL Server?

Ответ 1

Самый простой способ объяснить это - посмотреть, как FOR XML PATH работает для реального XML. Представьте себе простую таблицу Employee:

EmployeeID      Name
1               John Smith
2               Jane Doe

Вы можете использовать

SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')

Это создаст XML следующим образом

<Employee>
    <EmployeeID>1</EmployeeID>
    <Name>John Smith</Name>
</Employee>
<Employee>
    <EmployeeID>2</EmployeeID>
    <Name>Jane Doe</Name>
</Employee>

Удаление "Employee" из PATH удаляет внешние теги xml, чтобы этот запрос:

SELECT  Name
FROM    Employee
FOR XML PATH ('')

Создал бы

    <Name>John Smith</Name>
    <Name>Jane Doe</Name>

То, что вы тогда делаете, не является идеальным, имя столбца 'data()' вызывает ошибку sql, потому что он пытается создать тег xml, который не является юридическим тегом, поэтому возникает следующая ошибка:

Название столбца "Данные()" содержит недопустимый XML-идентификатор, как требуется FOR XML; '(' (0x0028) является первым символом при неисправности.

Коррелированный подзапрос скрывает эту ошибку и просто генерирует XML без тегов:

SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')

создает

John Smith Jane Doe

Затем вы заменяете пробелы запятыми, достаточно понятными...

Если бы я был вами, я бы немного адаптировал запрос:

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; 

Отсутствие псевдонима столбца будет означать, что тэги xml не создаются, а добавление запятой в запросе выбора означает, что любые имена с пробелами не вызовут ошибок, STUFF удалит первую запятую и пробел.

ДОПОЛНЕНИЕ

Чтобы уточнить, что сказал KM в комментарии, поскольку, похоже, это выглядит как еще несколько просмотров, правильным способом избежать символов XML было бы использовать .value следующим образом:

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; 

Ответ 2

Разделите его шаг за шагом - изнутри.

Шаг 1:

Запустите самый внутренний запрос и посмотрите, что он производит:

SELECT E2.ename AS 'data()' 
FROM emp AS e2 
WHERE e2.DEPTNO = 10
FOR XML PATH('')

Вы должны получить что-то вроде:

CLARK KING MILLER

Шаг 2:

REPLACE просто заменяет пробелы на , - таким образом, превращая ваш вывод в

CLARK, KING, MILLER

Шаг 3:

Внешний запрос получает значение deptno - плюс результаты внутреннего запроса - и дает окончательный результат.

Ответ 3

SQL Server 2017 делает это намного проще с new STRING_AGG. Недавно наткнулся на этот пост и переключил мою стратегию STUFF/FOR XML, чтобы использовать новую строковую функцию. Также избегает необходимости делать дополнительный JOIN/SUBQUERY и накладные расходы FOR XML (и проблемы с нечетным кодированием) и трудно интерпретировать SQL.

SELECT  E1.deptno, 
        STRING_AGG(E1.ename, ', ') AS allemp
FROM    EMP AS e1 
GROUP BY DEPTNO; 

Примечание. Также убедитесь, что проверить экземпляр STRING_SPLIT, чтобы работать с данными с разделителями SQL намного легче.

Ответ 4

Внешний запрос извлекает список номеров отделов, а затем подзапрос запускается для каждого номера отдела, чтобы вернуть все имена, принадлежащие этому отделу. Подзапрос использует оператор FOR XML для форматирования вывода в отдельный список, разделенный запятыми.