Create a User defined function like SQL server 2017 STRING_AGG on earlier versions

Question

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

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 ?

Haletky

Aggregate functions can be imported via DLLs. You have to write class in C# or other .NET language and then import it to SSMS. But you don't need such function, there's nice example how to aggregate strings using for xml clause. – Valoniah

Can't be done in T-SQL. You'd need dynamic SQL, and functions can't use that. There's a reason STRING_AGG was added to the language. A custom CLR aggregate can do it, but there's no need for you to reinvent the wheel in that case; plenty of examples of that on the web. – Mongrelize

Possible duplicate of How to use GROUP BY to concatenate strings in SQL Server? – Valoniah

@JeroenMostert, well can't be done is not quite true :-D (although I'd not recommend my "solution" :-D ) – Guerrilla

@Shnugo: Meh. I stand by the letter of my comment, in that you can't do it without rewriting the original query -- specifically, your approach requires duplicating the GROUP BY and introducing a subquery, neither of which STRING_AGG requires. As far as the spirit goes, though, I'll readily acknowledge your answer comes as close as you can get. – Mongrelize

@JeroenMostert It's more kind of a joke :-D – Guerrilla

Hi @MichałTurczyn thanks for this nice direction =) and no, it's not duplicates - i was showing how to do it - just looking for a function for essayer using on the future . – Haletky

@Shnugo: there are no jokes on Stack Overflow. Only bad hacks and people innocent enough to copy and paste them. :-P (I've written a few myself, no judgement here...) – Mongrelize

@JeroenMostert The idea to pass a sub-select as implicitly casted XML can be very usefull though. In this answer I provide a generic function creating an HTML table out of any SELECT you pass into. For the given case it was better to use the STUFF-FOR XML-hack directly within the statement, rather than call an UDF... – Guerrilla

Answer 1

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] 
FROM 0x4D5A90000300000004000000FF......000; --from GetHexString function
GO

and then,

CREATE AGGREGATE [STR_AGG] (@input nvarchar(200)) RETURNS nvarchar(max) 
EXTERNAL NAME [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 
GROUP BY a.Id

Thanks all =)

Haletky

Great that you've found a good solution! Your problem with the path might come from the fact, that SqlServer is not seeing the same c: as you in many installations – Guerrilla

One question: what about the sort order? You might add a parameter for this, otherwise this can be random – Guerrilla

Answer 2

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

CREATE FUNCTION dbo.MyStringAgg(@SelectForXmlAuto XML,@Delimiter NVARCHAR(10))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN STUFF((
             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),'');
END
GO

DECLARE @tbl TABLE(GroupId INT,SomeValue NVARCHAR(10));
INSERT INTO @tbl VALUES(1,'A1'),(1,'A2'),(2,'B1'),(3,'C1'),(3,'C2'),(3,'C3');

SELECT GroupId
      ,dbo.MyStringAgg((SELECT SomeValue 
                        FROM @tbl AS t2 
                        WHERE t2.GroupId=t.GroupId 
                        FOR XML AUTO), N', ')
FROM @tbl AS t
GROUP BY GroupId;
GO

DROP FUNCTION dbo.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

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

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

Guerrilla