SQL Server中的自定义聚合函数（concat） [英] Custom aggregate function (concat) in SQL Server

问题描述

问题：我想编写一个自定义聚合函数，将group by上的字符串连接起来。

Question: I want to write a custom aggregate function that concatenates string on group by.

这样我就可以做

SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2)  as f2
FROM TABLE_XY
GROUP BY FIELD1, FIELD2

我发现的只是SQL CRL聚合函数，但是我需要没有CLR的SQL。

All I find is SQL CRL aggregate functions, but I need SQL, without CLR.

编辑：1

查询应如下所示：

1
The query should look like this:

   SELECT SUM(FIELD1) as f1, MYCONCAT(FIELD2)  as f2
    FROM TABLE_XY
    GROUP BY FIELD0

编辑2：

确实，没有CLR是不可能的。

但是，可以修改astander的subselect答案，因此它不会对特殊字符进行XML编码。

Edit 2:
It is true that it isn't possible without CLR.
However, the subselect answer by astander can be modified so it doesn't XML-encode special characters.

对此的微妙更改是将其添加到 FOR XML PATH之后：
，

The subtle change for this is to add this after "FOR XML PATH": ,

 TYPE 
                  ).value('.[1]', 'nvarchar(MAX)') 

这里有几个例子

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   

推荐答案

您不能在CLR之外编写自定义聚合。

You cannot write custom aggregates outside of the CLR.

只能在纯T-SQL中编写的函数类型是标量和表值函数。

The only type of functions you can write in pure T-SQL are scalar and table valued functions.

比较创建汇总（仅列出CLR样式选项）和创建功能，其中显示了T-SQL和CLR选项。

Compare the pages for CREATE AGGREGATE, which only lists CLR style options, with CREATE FUNCTION, which shows T-SQL and CLR options.

这篇关于SQL Server中的自定义聚合函数（concat）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！