SQL Server中的自定义聚合函数（concat）

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，这是不可能的。但是，可以修改旁观者的子选择答案，因此不会对特殊字符进行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

6 个解决方案

#1

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选项）的页面。

#2

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

#3

Found this link around concatenation which covers methods like

在连接中找到这个链接，涵盖了类似的方法

Concatenating values when the number of items are not known

当项目数量未知时连接值

Recursive CTE method
递归CTE方法
The blackbox XML methods
黑盒XML方法
Using Common Language Runtime
使用公共语言运行时
Scalar UDF with recursion
带递归的标量UDF
Table valued UDF with a WHILE loop
表值UDF与WHILE循环
Dynamic SQL
动态SQL
The Cursor approach
Cursor的方法

Non-reliable approaches

不可靠的方法

Scalar UDF with t-SQL update extension
带有t-SQL更新扩展的标量UDF
Scalar UDF with variable concatenation in SELECT
在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函数，但在那里可能会有一些用于帮助解决问题的用法。

#4

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/

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 )

#5

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

#6

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

#1