Custom aggregate function (concat) in SQL Server

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.

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

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

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

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.