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   
22
demandé sur Stefan Steiger 2010-12-07 11:33:26

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.

11
répondu Damien_The_Unbeliever 2010-12-07 08:35:47

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
13
répondu Adriaan Stander 2010-12-07 08:36:11

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.

3
répondu kevchadders 2013-08-29 17:23:14

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 )
3
répondu Xilmiki 2015-11-10 15:39:23

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

1
répondu Konstantin Salavatov 2018-06-06 15:03:44

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 
0
répondu Bijimon 2013-11-25 20:42:17