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 li

Solution 1:

Well, this is an ugly hack, I have to go and wash my hands now, but it works (in a way :-D)

CREATEFUNCTIONdbo.MyStringAgg(@SelectForXmlAuto XML,@Delimiter NVARCHAR(10))
RETURNSNVARCHAR(MAX)
ASBEGINRETURNSTUFF((
             SELECT @Delimiter + A.nd.value(N'(@*)[1]',N'nvarchar(max)')
             FROM @SelectForXmlAuto.nodes(N'/*') AS A(nd)
             FOR XML PATH(''),TYPE
           ).value(N'.',N'nvarchar(max)'),1,LEN(@Delimiter),'');
ENDGODECLARE @tblTABLE(GroupId INT,SomeValue NVARCHAR(10));
INSERTINTO @tblVALUES(1,'A1'),(1,'A2'),(2,'B1'),(3,'C1'),(3,'C2'),(3,'C3');

SELECTGroupId
      ,dbo.MyStringAgg((SELECT SomeValue 
                        FROM @tbl AS t2 
                        WHERE t2.GroupId=t.GroupId 
                        FOR XML AUTO), N', ')
FROM @tblAStGROUPBYGroupId;
GODROPFUNCTIONdbo.MyStringAgg;

The result

1    A1, A2
2    B1
3    C1, C2, C3

The parameter is a FOR XML sub-select within paranthesis. This will implicitly pass the sub-selects result as an XML into the function.

To be honest: I would not use this myself...

A query like this

SELECTGroupId
      ,STUFF((SELECT N', ' + SomeValue 
              FROM @tbl AS t2 
              WHERE t2.GroupId=t.GroupId 
              FOR XML PATH,TYPE).value(N'.','nvarchar(max)'),1,2,'')
FROM @tblAStGROUPBYGroupId;

produces the same result and is almost the same amount of typing - but should be faster then calling a slow UDF...

Solution 2:

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

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.

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.).

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

CREATE ASSEMBLY [STR_AGG] 
FROM0x4D5A90000300000004000000FF......000; --from GetHexString function
GO

and then,

CREATEAGGREGATE[STR_AGG] (@input nvarchar(200)) RETURNSnvarchar(max) 
EXTERNALNAME[STR_AGG].C_STRING_AGG;`

Now it's done.

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

and used like :

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 
GROUPBY a.Id

Thanks all =)