How to write the T-SQL STRING_AGG function

Question

I need to write a STRING_AGG for my SQL Server 2014 master database. It is a built in function for SQL Server 2017 and Azure SQL.

I have an application that executes stored procedures in an Azure SQL database, some of which use STRING_AGG. In our local development instance, we use a database on our localhost running on an older version of SQL Server. The issue is, because SQL Server 2014 doesn't have access to STRING_AGG, the stored procedures we wrote for our Azure SQL database won't work locally.

The function needs to behave identically to the Azure SQL version so the stored procedures will work in both databases without breaking the local version. I cannot rewrite the stored procedures in the Azure database and I cannot upgrade the version on my localhost.

I've attempted to create a function with the sql_variant type as a parameter, after googling this is as close as I can get to an any type, but I might be incorrect. My function is close I think, but it just returns the result set I pass in. The function needs to work generically, so that it has no knowledge of the table from which the result set is passed in. Luckily, we never use STRING_AGG on a result set of more than one column, so that might make things a little easier. Here's my attempt so far

CREATE FUNCTION dbo.my_STRING_AGG 
     (@expr sql_variant,
      @separator NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN
        STUFF((SELECT @separator + CONVERT(NVARCHAR(MAX), @expr)
               FOR XML PATH('')), 1, 1, '')
END

For a further frame of reference, here is an example of how I use STRING_AGG in my stored procedures

SELECT 
    STRING_AGG(CAST(TaskCommentAuditId as VARCHAR(255)), ', ') 
FROM 
    TaskCommentAudit;

Suggestions on how to get my function working correctly or another approach?

Thanks!

You need to learn how to write a user-defined aggregation function (see https://learn.microsoft.com/en-us/sql/t-sql/statements/create-aggregate-transact-sql?view=sql-server-2017). It does not look anything like what you are doing. There are probably versions floating around the web. — Gordon Linoff, Jul 13 '18 at 19:34
Reading this it looks like I'll have to write it in C# and compile it down to a dll? There's no way to do this in T-SQL then? — pdaniels0013, Jul 13 '18 at 19:45
Looks like you want this, no?: https://stackoverflow.com/q/17591490/6167855 — S3S, Jul 13 '18 at 19:56
@scsimon, no, this is for a specific table, I need a function that works on any generic result set, like the string_agg function — pdaniels0013, Jul 13 '18 at 22:24

score 1 · Answer 1 · edited Apr 24 '19 at 19:48

Set the following external function in your database

    /*
GROUP_CONCAT string aggregate for SQL Server - https://groupconcat.codeplex.com
Copyright (C) 2011  Orlando Colamatteo

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or 
any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

See http://www.gnu.org/licenses/ for a copy of the GNU General Public 
License.
*/

/*
Installation script for GROUP_CONCAT functions. Tested in SSMS 2008R2.
*/
SET NOCOUNT ON ;
GO

-- !! MODIFY TO SUIT YOUR TEST ENVIRONMENT !!
USE GroupConcatTest
GO

-------------------------------------------------------------------------------------------------------------------------------
 =-- Turn advanced options on
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
 =-- Enable CLR
EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
-------------------------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT OFF;
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
GO
-------------------------------------------------------------------------------------------------------------------
PRINT N'Creating [GroupConcat]...';
GO
CREATE ASSEMBLY [GroupConcat]
    AUTHORIZATION [dbo]

    WITH PERMISSION_SET = SAFE;
GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END
IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END
GO
EXEC sys.sp_addextendedproperty 
    @name = N'URL',
    @value = N'http://groupconcat.codeplex.com',
    @level0type = N'ASSEMBLY',
    @level0name = N'GroupConcat'
GO
-------------------------------------------------------------------------------------------------------------------
PRINT N'Creating [dbo].[GROUP_CONCAT_D]...';
GO
CREATE AGGREGATE [dbo].[GROUP_CONCAT_D](@VALUE NVARCHAR (4000), @DELIMITER NVARCHAR (4))
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME [GroupConcat].[GroupConcat.GROUP_CONCAT_D];
GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END
IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END
GO
-------------------------------------------------------------------------------------------------------------------
PRINT N'Creating [dbo].[GROUP_CONCAT_S]...';
GO
CREATE AGGREGATE [dbo].[GROUP_CONCAT_S](@VALUE NVARCHAR (4000), @SORT_ORDER TINYINT)
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME [GroupConcat].[GroupConcat.GROUP_CONCAT_S];
GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END
IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END
GO
-------------------------------------------------------------------------------------------------------------------
PRINT N'Creating [dbo].[GROUP_CONCAT_DS]...';
GO
CREATE AGGREGATE [dbo].[GROUP_CONCAT_DS](@VALUE NVARCHAR (4000), @DELIMITER NVARCHAR (4), @SORT_ORDER TINYINT)
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME [GroupConcat].[GroupConcat.GROUP_CONCAT_DS];
GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END
IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END
GO
-------------------------------------------------------------------------------------------------------------------
PRINT N'Creating [dbo].[GROUP_CONCAT]...';
GO
CREATE AGGREGATE [dbo].[GROUP_CONCAT](@VALUE NVARCHAR (4000))
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME [GroupConcat].[GroupConcat.GROUP_CONCAT];
GO
IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
    END
IF @@TRANCOUNT = 0
    BEGIN
        INSERT  INTO #tmpErrors (Error)
        VALUES                 (1);
        BEGIN TRANSACTION;
    END
GO
-------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT N'The transacted portion of the database update succeeded.'
COMMIT TRANSACTION
END
ELSE PRINT N'The transacted portion of the database update failed.'
GO
DROP TABLE #tmpErrors
-------------------------------------------------------------------------------------------------------------------
GO

source

Then you can use them on the form

**select BlogId, dbo.GROUP_CONCAT(Title) from Posts 
group by BlogId**

ruselt

BlogId  (No column name)
1   Title 1,Title 2,Title 3,Title 11,Title 12,Title 13,Title 14,Title 
2   Title 29,Title 21,Title 10,Title 17,Title 15
3   Title 18,Title 5,Title 8,Title 28
4   Title 7,Title 19

It is necessary to run CLR on the server Thus, when the database is restored to a new server, the CLR must be run

-- Turn advanced options on
EXEC sys.sp_configure @configname = 'show advanced options', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
-- Enable CLR
EXEC sys.sp_configure @configname = 'clr enabled', @configvalue = 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO

Correct source available at https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr/blob/master/GroupConcat/Installation%20Scripts/GroupConcatInstallation.sql — Anton Krouglov, Jan 29 '21 at 11:19

How to write the T-SQL STRING_AGG function

1 Answers1

Linked