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    

Answer 1

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.

Answer 2

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