O que essa consulta faz para criar uma lista delimitada por vírgulas do SQL Server?

Eu escrevi essa consulta com a ajuda do google para criar uma lista delimitada de uma tabela, mas não entendi nada dessa consulta.

Alguém pode me explicar o que está acontecendo

SELECT E1.deptno, allemp = Replace ((SELECT E2.ename AS 'data()' FROM emp AS e2 WHERE e1.deptno = e2.DEPTNO FOR xml PATH('')), ' ', ', ') FROM EMP AS e1 GROUP BY DEPTNO; 

Me dá resultado

 10 CLARK, KING, MILLER 20 SMITH, JONES, SCOTT, ADAMS, FORD 30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES 

A maneira mais simples de explicá-lo é observar como o FOR XML PATH funciona para o XML real. Imagine uma mesa simples Employee :

 EmployeeID Name 1 John Smith 2 Jane Doe 

Você poderia usar

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

Isso criaria o XML da seguinte forma

  1 John Smith   2 Jane Doe  

Remover o ‘Employee’ do PATH remove as tags xml externas para que esta consulta:

 SELECT Name FROM Employee FOR XML PATH ('') 

Criaria

  John Smith Jane Doe 

O que você está fazendo então não é o ideal, o nome da coluna ‘data ()’ força um erro sql porque está tentando criar uma tag xml que não é uma tag legal, então o seguinte erro é gerado:

O nome da coluna ‘Data ()’ contém um identificador XML inválido, conforme exigido pelo FOR XML; ‘(‘ (0x0028) é o primeiro caractere com falha.

A subconsulta correlacionada oculta esse erro e apenas gera o XML sem tags:

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

cria

 John Smith Jane Doe 

Você está então substituindo espaços por vírgulas, bastante auto explicativas …

Se eu fosse você, eu adaptaria a consulta ligeiramente:

 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; 

Não ter um alias de coluna significa que nenhuma tag XML será criada, e adicionar a vírgula na consulta select significa que qualquer nome com espaços não causará erros, o STUFF removerá a primeira vírgula e espaço.

TERMO ADITIVO

Para elaborar o que KM disse em um comentário, já que isso parece estar recebendo mais algumas visualizações, a maneira correta de escaping de caracteres XML seria usar .value seguinte forma:

 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; 

Desmontá-lo passo a passo – de dentro para fora.

Passo 1:

Execute a consulta mais interna e veja o que ela produz:

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

Você deve obter uma saída como:

 CLARK KING MILLER 

Passo 2:

O REPLACE apenas substitui os espaços por -, transformando sua saída em

 CLARK, KING, MILLER 

Etapa 3:

A consulta externa obtém o valor deptno – mais os resultados da consulta interna – e produz o resultado final.

O SQL Server 2017 facilita isso com o novo STRING_AGG . Recentemente deparei com este post e troquei minha estratégia STUFF / FOR XML para usar a nova function de string. Também evita a necessidade de fazer um JOIN / SUBQUERY extra e a sobrecarga de FOR XML ( e os problemas de codificação ímpar ) e difíceis de interpretar SQL.

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

Nota : Certifique-se também de verificar a contraparte STRING_SPLIT para tornar o trabalho com dados delimitados por SQL muito mais fácil.

A consulta externa recupera uma lista de números de departamento e a subconsulta é executada para cada número de departamento para retornar todos os nomes pertencentes a esse departamento. A subconsulta usa a instrução FOR XML para formatar a saída em uma única linha separada por vírgula.