Te recomendamos que pruebes esta respuesta en un entorno controlado antes de enviarlo a producción, un saludo.
Solución:
Una técnica común para el agregado string concatenación antes de SQL Server 2017 usa métodos de tipo de datos XML junto con STUFF para eliminar el delimitador adicional. En SQL Server 2017 y versiones posteriores, STRING_AGG hace que esta tarea sea mucho más limpia y fácil. El STRING_AGG equivalente:
select @colsUnpivot = STRING_AGG(quotename (C.name),',')
from sys.columns c
where c.object_id = OBJECT_ID('dbo.result2')
Los componentes de la técnica de expresión XML para agregado string concatenación son las siguientes.
Esta subconsulta generará un conjunto de resultados con una fila por columna en la tabla de origen con una coma antes de cada nombre de columna:
select ',' + quotename (C.name)
from sys.columns c
where c.object_id = OBJECT_ID('dbo.result2')
Resultado de ejemplo:
,[column1]
,[column2]
,[column3]
Agregando el FOR XML PATH(''), TYPE convierte estas filas en un solo nodo de texto XML fuertemente tipado con las filas concatenadas, lo que permite métodos XML como value para ser utilizado en el tipo:
select ',' + quotename (C.name)
from sys.columns c
where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE
Resultado (tipo XML):
,[column1],[column2],[column3]
Invocando el método value('.', 'NVARCHAR(MAX)') en ese nodo XML convierte el nodo XML en un nvarchar (MAX) string. El value El método de tipo de datos XML toma una expresión XQuery con '.' que representa el nodo actual (solo el nodo aquí) y el segundo argumento es el tipo de datos SQL deseado que se devolverá.
SELECT
(select ',' + quotename (C.name)
from sys.columns c
where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)')
Resultado (nvarchar(MAX)):
,[column1],[column2],[column3]
Finalmente, la función STUFF elimina el delimitador principal extraño del string. Hay un error en su ejemplo original; la longitud debe ser 1 en lugar de 8 para que solo se elimine la coma:
SELECT
STUFF((select ',' + quotename (C.name)
from sys.columns c
where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
Resultado final (nvarchar(MAX)):
[column1],[column2],[column3]
A veces veo que la gente omite el , TYPE).value('.', 'NVARCHAR(MAX)') al utilizar esta técnica. El problema con eso es que algunos caracteres deben escaparse con referencias a entidades XML (por ejemplo, comillas) para que el resultado string no será como se esperaba en ese caso.
EDITAR:
Incorporando la recomendación de uso de Mister Magoo (./text())[1] en lugar de solo . como especificación del nodo, la consulta a continuación mejorará el rendimiento con grandes conjuntos de datos.
SELECT
STUFF((select ',' + quotename (C.name)
from sys.columns c
where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'');
Como dijo Dan, pero también…
Acostúmbrese a usar el nodo text() en valores xml, no hará mucha diferencia en un conjunto de resultados pequeño, pero no veo ninguna razón para NO usarlo, y realmente verá un beneficio en conjuntos de datos más grandes. .
La sección roja es dolorosa y se puede evitar mediante el uso de (./text())[1]
