在早期版本上创建用户定义的函数，例如SQL Server 2017 STRING_AGG [英] Create a User defined function like SQL server 2017 STRING_AGG on earlier versions

问题描述

我尝试创建一个可以像

I try to create a generic function that can be used like this example of using the new string_agg built-in function on SQL Server 2017

内部实现可以类似于以下内容

the inside implementation can be something like the follow

with tbl as(
   select a.Id, c.Desc
     from TableA a
     join TableB b on b.aId = a.Id
     join TableC c on c.Code = b.bCode 
)  
select distinct ID
     , STUFF(( select ', ' + Desc from tbl t where t.ID = tbl.ID 
            for xml path(''),TYPE).value('.','VARCHAR(MAX)'),1,2,'') Desc   
from tbl

但是如何接收字段键，要连接的字段，分隔符char和作用域选择上下文呢? 它与 Inline或Multi-Statement Table-Valued Functions 吗?

But how to receives the field key, the field to be connected, the separator char, and the scoped select context? Is it related to Inline or Multi-Statement Table-Valued Functions ?

推荐答案

好..因此，在@MichałTurczyn的第一条评论中，我遇到了

Ok.. so with the first comment of @MichałTurczyn I run into this Microsoft article about CLR User-Defined Aggregate - Invoking Functions

一旦我将代码编译到SrAggFunc.dll中，我就尝试在SQL Server中注册聚合，如下所示:

Once I compile the code into SrAggFunc.dll, I was trying to register the aggregate in SQL Server as follows:

CREATE ASSEMBLY [STR_AGG] FROM 'C:\tmp\STR_AGG.dll'; 
GO

但是我遇到了以下错误.

But I got the following error.

6501，第16级，状态7，第1行
CREATE ASSEMBLY失败，因为它无法打开物理文件'C:\ tmp \ SrAggFunc.dll':3(系统找不到指定的路径.).

Msg 6501, Level 16, State 7, Line 1
CREATE ASSEMBLY failed because it could not open the physical file 'C:\tmp\SrAggFunc.dll': 3(The system cannot find the path specified.).

因此，我使用了 此 @SanderRijken代码的重要部分，然后将命令更改为

So I used this excellant part of @SanderRijken code and then change the command to

CREATE ASSEMBLY [STR_AGG] 
FROM 0x4D5A90000300000004000000FF......000; --from GetHexString function
GO

然后

CREATE AGGREGATE [STR_AGG] (@input nvarchar(200)) RETURNS nvarchar(max) 
EXTERNAL NAME [STR_AGG].C_STRING_AGG;`

现在完成了.

您可以在数据库-> SSMS上的可编程性下看到它

You can see it under your Database -> Programmability on SSMS

，其用法类似于:

SELECT a.Id, [dbo].[STR_AGG](c.Desc) cDesc
FROM TableA a
JOIN TableB b on b.aId = a.Id
JOIN TableC c on c.Code = b.bCode 
GROUP BY a.Id

谢谢所有=)

这篇关于在早期版本上创建用户定义的函数，例如SQL Server 2017 STRING_AGG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！