如何编写T STRING_AGG函数
如何编写T STRING_AGG函数
提问于 2018-07-13 19:24:27
我需要为我的Server 2014主数据库编写一个STRING_AGG。它是Server 2017和Azure SQL的内置函数。
我有一个在Azure数据库中执行存储过程的应用程序，其中一些使用STRING_AGG。在本地开发实例中，我们使用本地主机上运行在较旧版本的Server上的数据库。问题是，由于Server 2014没有访问STRING_AGG的权限，我们为Azure SQL数据库编写的存储过程将无法在本地工作。
函数的行为必须与Azure SQL版本相同，这样存储过程就可以在两个数据库中工作，而不会破坏本地版本。I不能重写Azure数据库中的存储过程，而i不能在本地主机上升级版本。
我尝试用sql_variant类型作为参数创建一个函数，在谷歌搜索之后，这是我所能得到的尽可能接近any类型的函数，但是我可能不正确。我的函数很接近，但它只返回我传入的结果集。函数需要一般地工作，这样它就不知道结果集是从哪个表传入的。幸运的是，我们从未对多列的结果集使用STRING_AGG，因此这可能会使事情变得更容易一些。到目前为止，我的尝试如下
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作为进一步的参考框架，下面是我如何在存储过程中使用STRING_AGG的一个示例
SELECT
STRING_AGG(CAST(TaskCommentAuditId as VARCHAR(255)), ', ')
FROM
TaskCommentAudit;如何使我的功能正确工作或其他方法的建议？
谢谢!
回答 1
Stack Overflow用户
发布于 2018-07-14 13:29:07
在数据库中设置以下外部函数
/*
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然后，您可以在表单上使用它们。
**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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51331640
复制相关文章
相似问题
腾讯云开发者
