¿Qué hace esta consulta para crear una lista delimitada por comas en SQL Server?
Escribí esta consulta con la ayuda de Google para crear una lista delimitada a partir de una tabla, pero no entendí nada de esta consulta.
¿Alguien puede explicarme qué está pasando?
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 da resultado
10 CLARK, KING, MILLER
20 SMITH, JONES, SCOTT, ADAMS, FORD
30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES
La forma más sencilla de explicarlo es observar cómo FOR XML PATHfunciona para XML real. Imagina una tabla simple Employee:
EmployeeID Name
1 John Smith
2 Jane Doe
Podrías usar
SELECT EmployeeID, Name
FROM emp.Employee
FOR XML PATH ('Employee')
Esto crearía XML de la siguiente manera
<Employee>
<EmployeeID>1</EmployeeID>
<Name>John Smith</Name>
</Employee>
<Employee>
<EmployeeID>2</EmployeeID>
<Name>Jane Doe</Name>
</Employee>
Al eliminar el 'Empleado' se PATHeliminan las etiquetas xml externas, por lo que esta consulta:
SELECT Name
FROM Employee
FOR XML PATH ('')
Crearía
<Name>John Smith</Name>
<Name>Jane Doe</Name>
Lo que estás haciendo entonces no es ideal, el nombre de la columna 'data()' fuerza un error de SQL porque está intentando crear una etiqueta xml que no es una etiqueta legal, por lo que se genera el siguiente error:
El nombre de columna 'Data()' contiene un identificador XML no válido según lo exige FOR XML; '('(0x0028) es el primer carácter defectuoso.
La subconsulta correlacionada oculta este error y simplemente genera el XML sin etiquetas:
SELECT Name AS [Data()]
FROM Employee
FOR XML PATH ('')
crea
John Smith Jane Doe
Luego estás reemplazando espacios con comas, lo que se explica por sí mismo...
Si fuera usted, adaptaría ligeramente la consulta:
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;
No tener un alias de columna significará que no se crean etiquetas xml, y agregar la coma dentro de la consulta de selección significa que cualquier nombre con espacios no causará errores y STUFFeliminará la primera coma y el espacio.
APÉNDICE
Para profundizar en lo que KM ha dicho en un comentario, ya que parece que esto está recibiendo algunas vistas más, la forma correcta de escapar de los caracteres XML sería utilizar .valuela siguiente manera:
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;
Desmóntelo paso a paso, desde adentro hacia afuera.
Paso 1:
Ejecute la consulta más interna y vea lo que produce:
SELECT E2.ename AS 'data()'
FROM emp AS e2
WHERE e2.DEPTNO = 10
FOR XML PATH('')
Deberías obtener un resultado similar a:
CLARK KING MILLER
Paso 2:
Simplemente REPLACEreemplaza los espacios con ,, convirtiendo así su salida en
CLARK, KING, MILLER
Paso 3:
La consulta externa obtiene el deptnovalor (más los resultados de la consulta interna) y produce el resultado final.
SQL Server 2017 hace que esto sea mucho más fácil con el nuevoSTRING_AGG . Recientemente encontré esta publicación y cambié mi estrategia STUFF/FOR XML para usar la nueva función de cadena. También evita la necesidad de realizar un JOIN/SUBQUERY adicional y la sobrecarga de FOR XML ( y los problemas de codificación impar ) y SQL difícil de interpretar.
SELECT E1.deptno,
STRING_AGG(E1.ename, ', ') AS allemp
FROM EMP AS e1
GROUP BY DEPTNO;
Nota : asegúrese también de consultar la contraparteSTRING_SPLIT para facilitar mucho el trabajo con datos delimitados por SQL.
