分组将列值转换为以逗号分隔字符串
实现这篇之前,可以先参考这篇《数据表列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10848578.html
上这篇,只是输出一个值,现我们把数据表进行分组。
先把上一篇的源始数据再组织一下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL BEGIN DROP TABLE #tempTable END CREATE TABLE #tempTable ([ID] INT NOT NULL,[Type] NVARCHAR(20) NULL,[Category] NVARCHAR(40) NULL) INSERT INTO #tempTable ([ID],[Type],[Category]) VALUES (1,'TABLE','Table'), (2,'TABLE','View'), (3,'SP','Store Procedure'), (4,'FUN','Table-valued Function'), (5,'FUN','Scalar-valued Function'), (6,'TYPE','User-Defined Table Type') SELECT [ID],[Type],[Category] FROM #tempTable GO
接下来,再修改一下那个存储过程:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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_TableColumnValueToCommaDelimitedStringGoupBy] ( @tableName SYSNAME, @columnName SYSNAME, @GroupByColumnName SYSNAME ) AS BEGIN DECLARE @sql NVARCHAR(MAX) = N' SELECT '+ @GroupByColumnName +', STUFF( REPLACE( RTRIM( (SELECT ''|'' + CAST('+ @columnName +' AS NVARCHAR(MAX)) FROM '+ @tableName +' WHERE ('+ @GroupByColumnName +' = t.'+ @GroupByColumnName +') FOR XML PATH('''') ) ), ''|'','', ''), 1,1,'''') AS [multirow_comma-delimited_string] FROM '+ @tableName +' AS t GROUP BY '+ @GroupByColumnName +'' EXECUTE sp_executesql @sql END
举例说明: