strings - Función de agregado personalizado(concat) en SQL Server
sql server concat strings in select (6)
Pregunta: Quiero escribir una función de agregado personalizada que concatene cadena en grupo por.
Para que pueda hacer una
SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2
FROM TABLE_XY
GROUP BY FIELD1, FIELD2
Todo lo que encuentro son las funciones agregadas de SQL CRL, pero necesito SQL, sin CLR.
Editar: 1
La consulta debería verse así:
SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2
FROM TABLE_XY
GROUP BY FIELD0
Editar 2:
Es verdad que no es posible sin CLR.
Sin embargo, la respuesta de subselección de un espectador se puede modificar para que no codifique XML los caracteres especiales.
El cambio sutil para esto es agregar esto después de "FOR XML PATH":,
TYPE
).value(''.[1]'', ''nvarchar(MAX)'')
Aquí algunos ejemplos
DECLARE @tT table([A] varchar(200), [B] varchar(200));
INSERT INTO @tT VALUES (''T_A'', ''C_A'');
INSERT INTO @tT VALUES (''T_A'', ''C_B'');
INSERT INTO @tT VALUES (''T_B'', ''C_A'');
INSERT INTO @tT VALUES (''T_C'', ''C_A'');
INSERT INTO @tT VALUES (''T_C'', ''C_B'');
INSERT INTO @tT VALUES (''T_C'', ''C_C'');
SELECT
A AS [A]
,
(
STUFF
(
(
SELECT DISTINCT
'', '' + tempT.B AS wtf
FROM @tT AS tempT
WHERE (1=1)
--AND tempT.TT_Status = 1
AND tempT.A = myT.A
ORDER BY wtf
FOR XML PATH, TYPE
).value(''.[1]'', ''nvarchar(MAX)'')
, 1, 2, ''''
)
) AS [B]
FROM @tT AS myT
GROUP BY A
SELECT
(
SELECT
'',äöü<>'' + RM_NR AS [text()]
FROM T_Room
WHERE RM_Status = 1
ORDER BY RM_NR
FOR XML PATH('''')
) AS XmlEncodedNoNothing
,
SUBSTRING
(
(
SELECT
'',äöü<>'' + RM_NR AS [data()]
FROM T_Room
WHERE RM_Status = 1
ORDER BY RM_NR
FOR XML PATH('''')
)
,2
,10000
) AS XmlEncodedSubstring
,
(
STUFF
(
(
SELECT '',äöü<>'' + RM_NR + CHAR(10)
FROM T_Room
WHERE RM_Status = 1
ORDER BY RM_NR
FOR XML PATH, TYPE
).value(''.[1]'', ''nvarchar(MAX)'')
, 1, 1, ''''
)
) AS XmlDecodedStuffInsteadSubstring
A partir de 2017, hay una función agregada de concatenación incorporada STRING_AGG :)
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
Echa un vistazo a algo como. Esta no es una función agregada. Si desea implementar su propia función de agregado, tendrá que ser CLR ...
DECLARE @Table TABLE(
ID INT,
Val VARCHAR(50)
)
INSERT INTO @Table (ID,Val) SELECT 1, ''A''
INSERT INTO @Table (ID,Val) SELECT 1, ''B''
INSERT INTO @Table (ID,Val) SELECT 1, ''C''
INSERT INTO @Table (ID,Val) SELECT 2, ''B''
INSERT INTO @Table (ID,Val) SELECT 2, ''C''
--Concat
SELECT t.ID,
SUM(t.ID),
stuff(
(
select '','' + t1.Val
from @Table t1
where t1.ID = t.ID
order by t1.Val
for xml path('''')
),1,1,'''') Concats
FROM @Table t
GROUP BY t.ID
Encontré este link alrededor de la concatenación que cubre métodos como
Valores de concatenación cuando no se conoce el número de elementos
- Método CTE recursivo
- Los métodos de Blackbox XML
- Uso del Common Language Runtime
- UDF escalar con recursión
- Tabla de UDF con un bucle WHILE
- SQL dinámico
- El enfoque del Cursor
Enfoques no confiables
- UDF escalar con extensión de actualización t-SQL
- UDF escalar con concatenación variable en SELECT
A pesar de que no cubre las funciones de aggerate, puede ser de cierta utilidad utilizar la concatenación para ayudarlo con su problema.
Esta solución funciona sin necesidad de implementar Visual Studio o el archivo dll en el servidor.
Copiar y pegar, ¡y funciona!
http://groupconcat.codeplex.com/
dbo.GROUP_CONCAT(VALUE )
dbo.GROUP_CONCAT_D(VALUE ), DELIMITER )
dbo.GROUP_CONCAT_DS(VALUE , DELIMITER , SORT_ORDER )
dbo.GROUP_CONCAT_S(VALUE , SORT_ORDER )
No puede escribir agregados personalizados fuera del CLR.
El único tipo de funciones que puede escribir en T-SQL puro son funciones escalares y con valores de tabla.
Compare las páginas de CREATE AGGREGATE , que solo enumera las opciones de estilo de CLR, con CREATE FUNCTION , que muestra las opciones de T-SQL y CLR.
Podría hacer algo como lo que he hecho a continuación para crear una función de concatenación agregada personalizada en T-SQL puro. Obviamente, me he ido con un nombre de tabla codificado y un grupo por columna, pero debería ilustrar el enfoque. Probablemente exista alguna forma de hacer de esto una función verdaderamente genérica utilizando un TSQL dinámico construido a partir de parámetros de entrada.
/*
User defined function to help perform concatenations as an aggregate function
Based on AdventureWorks2008R2 SalesOrderDetail table
*/
--select * from sales.SalesOrderDetail
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N''fnConcatenate'')
DROP FUNCTION fnConcatenate
GO
CREATE FUNCTION fnConcatenate
(
@GroupByValue int
)
returnS varchar(8000)
as
BEGIN
DECLARE @SqlString varchar(8000)
Declare @TempStore varchar(25)
select @SqlString =''''
Declare @MyCursor as Cursor
SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select ProductID
From sales.SalesOrderDetail where SalesOrderID = @GroupByValue
order by SalesOrderDetailID asc
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @TempStore
WHILE @@FETCH_STATUS = 0
BEGIN
select @SqlString = ltrim(rtrim(@TempStore )) +'','' + ltrim(rtrim(@SqlString))
FETCH NEXT FROM @MyCursor INTO @TempStore
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
RETURN @SqlString
END
GO
select SalesOrderID, Sum(OrderQty), COUNT(*) as DetailCount , dbo.fnConcatenate(salesOrderID) as ConCatenatedProductList
from sales.SalesOrderDetail
where salesOrderID= 56805
group by SalesOrderID