¿Simulando la función MySQL group_concat en Microsoft SQL Server 2005?
Estoy intentando migrar una aplicación basada en MySQL a Microsoft SQL Server 2005 (no por elección propia, pero así es la vida).
En la aplicación original, utilizamos casi en su totalidad declaraciones compatibles con ANSI-SQL, con una excepción importante: utilizamos group_concatla función de MySQL con bastante frecuencia.
group_concat, por cierto, hace esto: dada una tabla de, digamos, nombres de empleados y proyectos...
SELECT empName, projID FROM project_members;
devoluciones:
ANDY | A100
ANDY | B391
ANDY | X010
TOM | A100
TOM | A510
... y esto es lo que obtienes con group_concat:
SELECT
empName, group_concat(projID SEPARATOR ' / ')
FROM
project_members
GROUP BY
empName;
devoluciones:
ANDY | A100 / B391 / X010
TOM | A100 / A510
Entonces, lo que me gustaría saber es: ¿Es posible escribir, digamos, una función definida por el usuario en SQL Server que emule la funcionalidad de group_concat?
Casi no tengo experiencia en el uso de UDF, procedimientos almacenados ni nada por el estilo, solo SQL directo, así que por favor opte por dar demasiadas explicaciones :)
No hay una manera REAL y fácil de hacer esto. Aunque hay muchas ideas por ahí.
El mejor que he encontrado :
SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
SELECT column_name + ','
FROM information_schema.columns AS intern
WHERE extern.table_name = intern.table_name
FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;
O una versión que funcione correctamente si los datos pueden contener caracteres como<
WITH extern
AS (SELECT DISTINCT table_name
FROM INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM extern
CROSS APPLY (SELECT column_name + ','
FROM INFORMATION_SCHEMA.COLUMNS AS intern
WHERE extern.table_name = intern.table_name
FOR XML PATH(''), TYPE) x (column_names)
CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names)
Puede que llegue un poco tarde a la fiesta, pero este método STUFF() + FOR XML me funciona y es más fácil que el método COALESCE.
SELECT STUFF(
(SELECT ',' + Column_Name
FROM Table_Name
FOR XML PATH (''))
, 1, 1, '')
SQL Server 2017 introduce una nueva función agregada
STRING_AGG ( expression, separator).
Concatena los valores de expresiones de cadena y coloca valores separadores entre ellos. El separador no se agrega al final de la cadena.
Los elementos concatenados se pueden ordenar añadiendoWITHIN GROUP (ORDER BY some_expression)
Para las versiones 2005-2016 normalmente uso el método XML en la respuesta aceptada.
Sin embargo, esto puede fallar en algunas circunstancias. por ejemplo, si los datos que se van a concatenar contienen, CHAR(29)verá
FOR XML no pudo serializar los datos... porque contiene un carácter (0x001D) que no está permitido en XML.
Un método más sólido que pueda manejar todos los caracteres sería utilizar un agregado CLR. Sin embargo, aplicar un orden a los elementos concatenados es más difícil con este enfoque.
El método de asignación a una variable no está garantizado y debe evitarse en el código de producción.
Posiblemente ya sea demasiado tarde para que resulte beneficioso, pero ¿no es ésta la forma más fácil de hacer las cosas?
SELECT empName, projIDs = replace
((SELECT Surname AS [data()]
FROM project_members
WHERE empName = a.empName
ORDER BY empName FOR xml path('')), ' ', REQUIRED SEPERATOR)
FROM project_members a
WHERE empName IS NOT NULL
GROUP BY empName
Echa un vistazo al proyecto GROUP_CONCAT en Github, creo que hago exactamente lo que estás buscando:
Este proyecto contiene un conjunto de funciones agregadas definidas por el usuario SQLCLR (UDA SQLCLR) que en conjunto ofrecen una funcionalidad similar a la función MySQL GROUP_CONCAT. Existen múltiples funciones para garantizar el mejor rendimiento según la funcionalidad requerida...
