Custom aggregate function (concat) in SQL Server
Solution 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.
Solution 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
Solution 3
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
Solution 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.
Solution 5
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 )
![Stefan Steiger](https://i.stack.imgur.com/MaCKR.jpg?s=256&g=1)
Stefan Steiger
I'm an avid HTTP-header-reader, github-user and a few more minor things like BusinessIntelligence & Web Software Developer Technologies I work with: Microsoft Reporting- & Analysis Service (2005-2016), ASP.NET, ASP.NET MVC, .NET Core, ADO.NET, JSON, XML, SOAP, Thrift ActiveDirectory, OAuth, MS Federated Login XHTML5, JavaScript (jQuery must die), ReverseAJAX/WebSockets, WebGL, CSS3 C#, .NET/mono, plain old C, and occasional C++ or Java and a little Bash-Scripts, Python and PHP5 I have a rather broad experience with the following relational SQL databases T-SQL PL/PGsql including CLR / extended stored procedures/functions Occasionally, I also work with MySQL/MariaDB Firebird/Interbase Oracle 10g+ SqLite Access I develop Enterprise Web-Applications (.NET 2.0 & 4.5) and interface to systems like LDAP/AD (ActiveDirectory) WebServices (including WCF, SOAP and Thrift) MS Federated Login OAuth DropBox XML & JSON data-stores DWG/SVG imaging for architecture In my spare-time, I'm a Linux-Server-Enthusiast (I have my own Web & DNS server) and reverse-engineer with interest in IDS Systems (IntrusionDetection), WireShark, IDA Pro Advanced, GDB, libPCAP. - Studied Theoretical Physics at the Swiss Federal Institute of Technology (ETHZ).
Updated on February 09, 2021Comments
-
Stefan Steiger over 3 years
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