Custom Aggregate Function (Concat) in SQL Server

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.

Does T-SQL have an aggregate function to concatenate strings?

for SQL Server 2017 and up use:

STRING_AGG()

set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SELECT
    HeaderValue, STRING_AGG(ChildValue,', ')
    FROM @YourTable
    GROUP BY HeaderValue

OUTPUT:

HeaderValue 
----------- -------------
1           CCC
2           B<&>B, AAA
3           <br>, A & Z

(3 rows affected)

for SQL Server 2005 and up to 2016, you need to do something like this:

--Concatenation with FOR XML and eleminating control/encoded character expansion "& < >"
set nocount on;
declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))
insert into @YourTable VALUES (1,1,'CCC')
insert into @YourTable VALUES (2,2,'B<&>B')
insert into @YourTable VALUES (3,2,'AAA')
insert into @YourTable VALUES (4,3,'<br>')
insert into @YourTable VALUES (5,3,'A & Z')
set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

OUTPUT:

HeaderValue ChildValues
----------- -------------------
1           CCC
2           AAA, B<&>B
3           <br>, A & Z

(3 row(s) affected)

Also, watch out, not all FOR XML PATH concatenations will properly handle XML special characters like my above example will.

Optimal way to concatenate/aggregate strings

SOLUTION

The definition of optimal can vary, but here's how to concatenate strings from different rows using regular Transact SQL, which should work fine in Azure.

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM dbo.SourceTable
),
Concatenated AS
(
    SELECT 
        ID, 
        CAST(Name AS nvarchar) AS FullName, 
        Name, 
        NameNumber, 
        NameCount 
    FROM Partitioned 
    WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, 
        CAST(C.FullName + ', ' + P.Name AS nvarchar), 
        P.Name, 
        P.NameNumber, 
        P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C 
                ON P.ID = C.ID 
                AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

EXPLANATION

The approach boils down to three steps:

1. Number the rows using OVER and PARTITION grouping and ordering them as needed for the concatenation. The result is Partitioned CTE. We keep counts of rows in each partition to filter the results later.

2. Using recursive CTE (Concatenated) iterate through the row numbers (NameNumber column) adding Name values to FullName column.

3. Filter out all results but the ones with the highest NameNumber.

Please keep in mind that in order to make this query predictable one has to define both grouping (for example, in your scenario rows with the same ID are concatenated) and sorting (I assumed that you simply sort the string alphabetically before concatenation).

I've quickly tested the solution on SQL Server 2012 with the following data:

INSERT dbo.SourceTable (ID, Name)
VALUES 
(1, 'Matt'),
(1, 'Rocks'),
(2, 'Stylus'),
(3, 'Foo'),
(3, 'Bar'),
(3, 'Baz')

The query result:

ID          FullName
----------- ------------------------------
2           Stylus
3           Bar, Baz, Foo
1           Matt, Rocks

Custom Aggregate function for SQL Server

With SQL CLR (the .NET integration into SQL Server) : yes

You can create your own CLR User-Defined Aggregates.

Create a User defined function like SQL server 2017 STRING_AGG on earlier versions

Ok.. so with the first comment of @MichałTurczyn I run into this Microsoft article about CLR User-Defined Aggregate - Invoking Functions

Once I compile the code into SrAggFunc.dll, I was trying to register the aggregate in SQL Server as follows:

CREATE ASSEMBLY [STR_AGG] FROM 'C:\tmp\STR_AGG.dll'; 
GO

But I got the following error.

Msg 6501, Level 16, State 7, Line 1

CREATE ASSEMBLY failed because it could not open the physical file 'C:\tmp\SrAggFunc.dll': 3(The system cannot find the path specified.).

So I used this excellant part of @SanderRijken code and then change the command to

CREATE ASSEMBLY [STR_AGG] 
FROM 0x4D5A90000300000004000000FF......000; --from GetHexString function
GO

and then,

CREATE AGGREGATE [STR_AGG] (@input nvarchar(200)) RETURNS nvarchar(max) 
EXTERNAL NAME [STR_AGG].C_STRING_AGG;`

Now it's done.

You can see it under your Database -> Programmability on SSMS

and used like :

SELECT a.Id, [dbo].[STR_AGG](c.Desc) cDesc
FROM TableA a
JOIN TableB b on b.aId = a.Id
JOIN TableC c on c.Code = b.bCode 
GROUP BY a.Id

Thanks all =)

How to use GROUP BY to concatenate strings in SQL Server?

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

SQL Server error using the CONCAT function in a pivot aggregated function

Try this,

CREATE TABLE #PIVOT (Time  DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))

insert into #PIVOT values 
 ('2018-05-07 16:00:00.000', 02, 11     ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2      ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7      ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8      ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9      ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9      ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8      ,'FIA')


select
 Time, CARs + convert (varchar (10), [02]) [02]
 , CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
 select
 *
 from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t

Time                        02      06      10
2018-05-07 16:00:00.000     BMW11   NULL    NULL
2018-05-07 16:15:00.000     BMW2    NULL    NULL
2018-05-07 16:45:00.000     BMW9    NULL    NULL
2018-05-07 17:00:00.000     BMW9    NULL    NULL
2018-05-07 17:00:00.000     NULL    NULL    FIA8
2018-05-07 16:15:00.000     NULL    KIA7    NULL
2018-05-07 16:30:00.000     NULL    KIA8    NULL

Let me work for dynamic query.

---

Related Topics