Custom aggregate function (concat) in SQL Server

Question

Question: I want to write a custom aggregate function that concatenates string on 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.

Edit:1
The query should look like this:

   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.

The subtle change for this is to add this after "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

In the case of your example code there will only be one value for FIELD2 anyway (GROUP BY) so you don't need the function. I guess your example is wrong. — nvogel, Dec 07 '10 at 08:43
Ahahaha, good one - damn, you're right. Field0 would be a UID (group by), field1 and field2 shouldn't be in the group clause... — Stefan Steiger, Dec 07 '10 at 14:39

score 17 · Accepted Answer · answered Dec 07 '10 at 08:35

17

You cannot write custom aggregates outside of the CLR.

The only type of functions you can write in pure T-SQL are scalar and table valued functions.

Compare the pages for CREATE AGGREGATE, which only lists CLR style options, with CREATE FUNCTION, which shows T-SQL and CLR options.

answered Dec 07 '10 at 08:35

Damien_The_Unbeliever

234,701
27
340
448

1

The problem is I can't give a CLR function to a customer for installation, especially not in a restricted security area... But anyway, 'not possible' answers my question. – Stefan Steiger Dec 07 '10 at 08:41
2

@Quandary Just a suggestion: If you tell a db admin he needs to enable clr in sql and load your assembly then of course he's going to say "Oh, that's a security risk". But if you have your app do all that work and just say "The app needs these rights" (or better yet, just have it in an installer so only the installer needs those rights) then you're more likely to get approval. Maybe not an option for you at all (and I realize this is a year old question) but I thought I'd just throw that thought out there. – Brandon Moore Nov 20 '11 at 06:43
@Brandon Moore: ;-) Yes, you successfully identified the problem between the lines - suggesting politics to the rescue. However, I very much doubt any sufficiently intelligent DB admin would give any application the rights to alter DB-wide settings... – Stefan Steiger Nov 21 '11 at 18:43
@Quandary It's all in the pricing. Give them incentive to open up the CLR for you and if they decline then at least it was worth your while to do it the other way ;) – Brandon Moore Nov 21 '11 at 20:21
1

@Brandon Moore: It's worth my while when the work gets paid. Otherwise it's just a piece of superfluous work/expenditure that nobody pays. Bottom line is that this is NOT in the agreed-on pricing. – Stefan Steiger Jan 27 '14 at 06:47

score 14 · Answer 2 · answered Dec 07 '10 at 08:36

14

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

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

answered Dec 07 '10 at 08:36

Adriaan Stander

162,879
31
289
284

1

The table variable is given as an example... Replace it with your table you wish to aggregate. – Adriaan Stander Dec 07 '10 at 08:46
@Quandary: I don't actually understand your complain. The temp table is just to show how it works. I don't know xml path, but why does it matter if it is unreadable, unintuitive etc. if you get a short, simple, working solution for your problem? – Stefan Steinegger Dec 07 '10 at 08:51
+1 @astander The more I try your solution, the more I like it. It works immediatly no preparation required. And until now I didn't see a complete ready to use CLR solution. (My first impression was that it was slow, but that was caused by some artefact) – bernd_k Dec 23 '10 at 08:48
@bernd_k: It's not slow, but it does XML-encode some letters, and you then have to do a felt 1000's replaces over the result (actually about 4, but it's the brackets that are the problem, if I liked bracket abundance, I'd program in LISP). – Stefan Steiger May 10 '13 at 07:51
@bernd_k: Found a simpler way to address that problem. Added it in edit2. – Stefan Steiger Apr 07 '14 at 10:08

score 14 · Answer 3 · answered Jun 06 '18 at 15:03

14

Starting from 2017 there is built-in concatenate aggregate function STRING_AGG :)

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

answered Jun 06 '18 at 15:03

Konstantin Salavatov

4,370
2
25
24

1

The question was tagged SQL Server 2008. Most people aren't yet using SQL Server 2017 anyway. – bitoolean Feb 01 '19 at 15:39
1

@bitoolean, many people think about migration to managed SQL Databases in Azure where no CLR and extended SPs – Konstantin Salavatov Feb 27 '19 at 15:32
1

Now that you mentioned that, it makes all the sense. Thank you for explaining! Your answer deserves an up-vote in that case. – bitoolean Mar 12 '19 at 02:23

score 4 · Answer 4 · edited Feb 08 '21 at 18:27

4

This solution works with no need of deploy from Visual studio or dll file in server.

Copy-Paste and it Work!

https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

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 )

edited Feb 08 '21 at 18:27

Trisped

5,705
2
45
58

answered Nov 10 '15 at 15:39

Xilmiki

1,453
15
22

2

This still requires enabling CLR in SQL Server and deploying a DLL. – thomas Feb 16 '18 at 15:53
@thomas is correct. The subtitle on that page even says so - "SQL Server CLR user-defined aggregates"… – bitoolean Feb 01 '19 at 15:36
This library is released under GNU/GPL licence, so be careful when using in commercial applications. – Bohdan Oct 04 '21 at 08:11

score 4 · Answer 5 · edited Aug 29 '13 at 17:23

4

Found this link around concatenation which covers methods like

Concatenating values when the number of items are not known

Recursive CTE method
The blackbox XML methods
Using Common Language Runtime
Scalar UDF with recursion
Table valued UDF with a WHILE loop
Dynamic SQL
The Cursor approach

Non-reliable approaches

Scalar UDF with t-SQL update extension
Scalar UDF with variable concatenation in SELECT

Though it doesn't cover aggerate functions there may be some use around concatenation in there to help you with your problem.

edited Aug 29 '13 at 17:23

Martin Smith

438,706
87
741
845

answered Dec 07 '10 at 08:40

kevchadders

8,335
4
42
61

4

@Quandary - The 2 best ways of doing this task **are** `XML PATH` and `CLR AGGREGATES`. If you dismiss both of these then you will be left scrabbling around the far worse remaining solutions in the list above. There are no other ways. – Martin Smith Dec 07 '10 at 11:22
@Martin Smith: Finally found the correct solution to the problem. See edit 2. – Stefan Steiger Apr 07 '14 at 10:10

score 0 · Answer 6 · edited Nov 25 '13 at 20:42

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.

/*
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

@Quandary; What dynamic SQL? – AMissico Dec 10 '14 at 23:34 — AMissico, Dec 10 '14 at 23:34

Custom aggregate function (concat) in SQL Server

6 Answers6

Linked

Related