数据表列值转换为逗号分隔字符串
在开发SQL Server语序中,可能需要这样一个要求,把表中某一列的所有值转换为使用逗号分隔的字符串去呈现出来。
举个例子:
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL BEGIN DROP TABLE #tempTable END CREATE TABLE #tempTable ([ID] INT NOT NULL,[Category] NVARCHAR(40) NULL) INSERT INTO #tempTable ([ID],[Category]) VALUES (1,'Table'), (2,'View'), (3,'Store Procedure'), (4,'Table-valued Function'), (5,'Scalar-valued Function'), (6,'User-Defined Table Type') SELECT [ID],[Category] FROM #tempTable GO
要求结果,[ID]或[Category]任意一列,呈现如下:
日后,我们不清楚是哪一张表,哪一个字段。
因此,可以写成一个动态的SQL 存储过程来处理:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Create date: 2019-05-11 -- Update date: 2019-05-11 -- Description: 列值转换为逗号分隔字符串 -- ============================================= CREATE PROCEDURE [dbo].[usp_TableColumnValueToCommaDelimitedString] ( @tableName SYSNAME, @columnName SYSNAME, @ReturnValue NVARCHAR(MAX) OUTPUT ) AS BEGIN DECLARE @sql NVARCHAR(MAX) = N' DECLARE @temporary_table AS TABLE([multirow_comma-delimited_string] NVARCHAR(MAX)) INSERT INTO @temporary_table ([multirow_comma-delimited_string]) SELECT TOP(1) STUFF( REPLACE( RTRIM( (SELECT ''|'' + CAST('+ @columnName +' AS NVARCHAR(MAX)) FROM '+ @tableName +' FOR XML PATH('''') ) ), ''|'','', ''), 1,1,'''') FROM '+ @tableName +' SELECT @ReturnValue = [multirow_comma-delimited_string] FROM @temporary_table' EXECUTE sp_executesql @sql, N'@ReturnValue NVARCHAR(MAX) OUTPUT', @ReturnValue OUTPUT END
只要为上面存储过程传入表名,字段名等参数即可得到我们想要的结果:
以下内容于2019-05-22 08:48:44.633更新:
存储过程,另一个版本:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_TableColumnValueToCommaDelimitedString] ( @tableName SYSNAME, @columnName SYSNAME, @Comma_Delimited_Column_Names NVARCHAR(MAX) OUTPUT ) AS BEGIN DECLARE @query NVARCHAR(MAX) = N'SET @Comma_Delimited_Column_Names = STUFF((SELECT DISTINCT '','' + QUOTENAME('+ @columnName +') FROM '+ @tableName +' FOR XML PATH(''''), TYPE ).value(''.'', ''NVARCHAR(MAX)'') ,1,1,'''')' EXECUTE sp_executeSql @query, N'@Comma_Delimited_Column_Names AS NVARCHAR(MAX) OUTPUT',@Comma_Delimited_Column_Names OUTPUT END
得到的结果,就是使用QUOTENAME函数,把每一个列名使用"["和"]"括起来: