如何编写T-SQL STRING_AGG函数
我需要为我的SQL Server 2014主数据库写一个STRING_AGG.它是SQL Server 2017和Azure SQL的内置函数.
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.
我有一个应用程序，该应用程序在Azure SQL数据库中执行存储过程，其中一些使用STRING_AGG.在本地开发实例中，我们在运行于旧版SQL Server上的本地主机上使用一个数据库.问题是，因为SQL Server 2014无法访问STRING_AGG，所以我们为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.
该函数的行为必须与Azure SQL版本相同，因此存储过程将在两个数据库中都可以工作而不会破坏本地版本.我无法重写Azure数据库中的存储过程，并且无法升级本地主机上的版本.
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.
我尝试创建一个以sql_variant类型作为参数的函数，在进行谷歌搜索之后，这与我可以尽可能接近any类型，但是我可能不正确.我认为我的函数很接近，但是它只返回我传入的结果集.该函数需要通用地工作，因此它不知道传入结果集的表.幸运的是，我们从不使用
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
作为进一步的参考，这是我如何在存储过程中使用STRING_AGG的示例
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?
谢谢！
在数据库中设置以下外部函数
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
然后您可以在表单上使用它们
Then you can use them on the form
**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 因此，当数据库还原到新服务器时，必须运行CLR
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