数据表列值转换为逗号分隔字符串

时间:2019-05-11 14:28:00 来源:互联网 作者: 神秘的大神 字体:

在开发SQL Server语序中,可能需要这样一个要求,把表中某一列的所有值转换为使用逗号分隔的字符串去呈现出来。

举个例子:

<img src="https://img2018.cnblogs.com/blog/28036/201905/28036-20190511141253323-907952627.png" alt="">

 

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]任意一列,呈现如下:

<img src="https://img2018.cnblogs.com/blog/28036/201905/28036-20190511141626486-2052745224.png" alt="">

 

日后,我们不清楚是哪一张表,哪一个字段。

因此,可以写成一个动态的SQL 存储过程来处理:

<img src="https://img2018.cnblogs.com/blog/28036/201905/28036-20190511142034540-1844551681.png" alt="">

 

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

 

只要为上面存储过程传入表名,字段名等参数即可得到我们想要的结果:

<img src="https://img2018.cnblogs.com/blog/28036/201905/28036-20190511142354650-698127880.png" alt="">