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 ?

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. — Michał Turczyn
– Michał Turczyn, Commented Feb 14, 2018 at 9:05
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. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 14, 2018 at 9:07
Possible duplicate of How to use GROUP BY to concatenate strings in SQL Server? — Michał Turczyn
– Michał Turczyn, Commented Feb 14, 2018 at 9:08
@JeroenMostert, well can't be done is not quite true :-D (although I'd not recommend my "solution" :-D ) — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 14, 2018 at 10:34
@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... — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 14, 2018 at 10:51

Community · Accepted Answer · 2020-06-20 09:12:55Z

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

oCcSking · Accepted Answer · 2018-02-15 16:35:47Z

4

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

edited Feb 15, 2018 at 16:35

answered Feb 15, 2018 at 9:59

oCcSking

9281 gold badge22 silver badges43 bronze badges

2 Comments

Gottfried Lesigang Over a year ago

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

Gottfried Lesigang Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

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

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

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

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related