Fonction d'agrégation personnalisée (concat) dans le serveur SQL
Question: je veux écrire une fonction d'agrégation personnalisée qui concaténate la chaîne sur le groupe par.
pour que je puisse faire un
SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2
FROM TABLE_XY
GROUP BY FIELD1, FIELD2
Tout ce que je trouve C'est des fonctions d'agrégat SQL CRL, mais J'ai besoin de SQL, sans CLR.
Edit: 1
La requête devrait ressembler à ceci:
SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2) as f2
FROM TABLE_XY
GROUP BY FIELD0
Edit 2:
C'est vrai que c' n'est pas possible sans CLR.
Cependant, la réponse subsélectionnée par astander peut être modifiée de façon à ce qu'elle ne Code pas de caractères spéciaux XML.
le changement subtil pour ceci est d'ajouter ceci après " pour le chemin XML": ,
TYPE
).value('.[1]', 'nvarchar(MAX)')
voici quelques exemples
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
6 réponses
vous ne pouvez pas écrire des agrégats personnalisés en dehors du CLR.
le seul type de fonctions que vous pouvez écrire en T-SQL pur sont les fonctions scalar et table valued.
comparez les pages pour CREATE AGGREGATE, qui ne Liste que les options de style CLR, avec CREATE FUNCTION, qui montre les options T-SQL et CLR.
jetez un oeil à quelque chose comme. Ce n'est pas une fonction d'agrégation. Si vous souhaitez mettre en œuvre votre propre fonction agrégée, il devra être 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
Trouvé ce lien autour de la concaténation qui couvre des méthodes comme
valeurs Concaténantes lorsque le nombre d'éléments n'est pas connu
- méthode Cte récursive
- La blackbox méthodes XML
- À L'Aide De Common Language Runtime
- UDF scalaire avec recursion
- Table UDF avec une boucle WHILE
- Dynamic SQL
- Le Curseur approche
approches non fiables
- UDF scalaire avec extension de mise à jour T-SQL
- UDF scalaire avec concaténation variable dans SELECT
bien qu'il ne couvre pas les fonctions agressives, il peut y avoir une certaine utilisation autour de la concaténation pour vous aider avec votre problème.
cette solution fonctionne avec aucun besoin de déployer à partir de Visual studio ou le fichier dll dans le serveur.
Copier-Coller et il du Travail!
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 )
A partir de 2017 il y a une fonction agrégat concatenate intégrée STRING_AGG:)
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017
Vous pouvez faire quelque chose comme ce que j'ai fait ci-dessous pour créer une fonction de concaténation agrégée personnalisée en T-SQL pur. Évidemment, je suis allé avec un nom de table codé dur et groupe par colonne, mais il devrait illustrer l'approche. Il y a probablement un moyen de faire de cette fonction une fonction vraiment générique en utilisant des TSQL dynamiques construits à partir de paramètres d'entrée.
/*
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