代码之家 › 专栏 › 技术社区 › pdaniels0013

如何编写T-SQL STRING_AGG函数

sql-server-2014 azure-sql-database tsql sql-server sql

1

pdaniels0013 · 技术社区 · 7 年前

我需要写一个 STRING_AGG

我有一个在Azure SQL数据库中执行存储过程的应用程序,其中一些使用 字符串聚集 字符串聚集 ,我们为Azure SQL数据库编写的存储过程将无法在本地工作。

不能重写Azure数据库中的存储过程,然后不能升级本地主机上的版本。

sql_variant 输入作为参数,在google之后,这是最接近的 any 键入,但我可能不正确。我认为我的函数很接近,但它只返回我传入的结果集。函数需要以泛型方式工作,这样它就不知道从中传递结果集的表。幸运的是,我们从不使用 字符串聚集 在一个包含多个列的结果集上,这样可能会使事情变得简单一些。这是我迄今为止的尝试

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

为了进一步的参考,下面是一个我如何使用 字符串聚集 在我的存储过程中

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

关于如何使我的功能正常工作的建议或其他方法?

谢谢!

1 回复 | 直到 7 年前

1

0

KT12 7 年前

在数据库中设置以下外部函数

    /*
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

然后你可以在表格上使用它们

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

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

必须在服务器上运行CLR

-- 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