SQL Server 中的自定义聚合函数 (concat)

Custom aggregate function (concat) in SQL Server

提问by Stefan Steiger

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

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

So that I can do a

这样我就可以做一个

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

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

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

Edit:1
The query should look like this:

编辑：1
查询应如下所示：

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

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.

编辑 2：
确实，没有 CLR 是不可能的。
但是，可以修改 astander 的 subselect 答案，因此它不会对特殊字符进行 XML 编码。

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

对此的细微变化是在“FOR XML PATH”之后添加：

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

Here a few examples

这里有几个例子

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   

采纳答案by Damien_The_Unbeliever

You cannot write custom aggregates outside of the CLR.

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

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

您可以在纯 T-SQL 中编写的唯一类型的函数是标量和表值函数。

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

将CREATE AGGREGATE的页面（仅列出 CLR 样式选项）与CREATE FUNCTION 的页面（显示 T-SQL 和 CLR 选项）进行比较。

回答by Adriaan Stander

Have a look at something like. This is not an aggregate function. If you wish to implement your own aggregate function, it will have to be CLR...

看看类似的东西。这不是聚合函数。如果您希望实现自己的聚合函数，则必须是 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

回答by Konstantin Salavatov

Starting from 2017 there is built-in concatenate aggregate function STRING_AGG :)

从 2017 年开始，有内置的连接聚合函数 STRING_AGG :)

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

回答by kevchadders

Found this linkaround concatenation which covers methods like

找到了这个关于连接的链接，它涵盖了类似的方法

Concatenating values when the number of items are not known

当项目数未知时连接值

• Recursive CTE method
• The blackbox XML methods
• Using Common Language Runtime
• Scalar UDF with recursion
• Table valued UDF with a WHILE loop
• Dynamic SQL
• The Cursor approach

• 递归 CTE 方法
• 黑盒 XML 方法
• 使用公共语言运行时
• 带递归的标量 UDF
• 带有 WHILE 循环的表值 UDF
• 动态 SQL
• 光标方法

Non-reliable approaches

不可靠的方法

• Scalar UDF with t-SQL update extension
• Scalar UDF with variable concatenation in SELECT

• 带有 t-SQL 更新扩展的标量 UDF
• SELECT 中带有变量连接的标量 UDF

Though it doesn't cover aggerate functions there may be some use around concatenation in there to help you with your problem.

虽然它不包括 aggerate 函数，但在那里可能会使用一些连接来帮助您解决问题。

回答by Xilmiki

This solution works with no need of deploy from Visual studio or dll file in server.

此解决方案无需从 Visual Studio 或服务器中的 dll 文件进行部署。

Copy-Paste and it Work!

复制粘贴和它的工作！

http://groupconcat.codeplex.com/

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 )

回答by Bijimon

You could do something like what I have done below to create a custom aggregate concatenation function in pure T-SQL. Obviously I have gone with a hard coded table name and group by column but it should illustrate the approach. There is probably some way to make this a truly generic function using dynamic TSQL constructed from input parameters.

您可以像我在下面所做的那样在纯 T-SQL 中创建自定义聚合连接函数。显然，我使用了硬编码的表名和按列分组，但它应该说明了该方法。可能有一些方法可以使用从输入参数构造的动态 TSQL 使其成为真正的通用函数。

/*
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