如何编写T

本文介绍了如何编写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 - groupconcat.codeplex 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 www.gnu/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'groupconcat.codeplex', @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