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