分组将列值转换为以逗号分隔字符串

实现这篇之前，可以先参考这篇《数据表列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10848578.html

上这篇，只是输出一个值，现我们把数据表进行分组。

先把上一篇的源始数据再组织一下：

 

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

 

接下来，再修改一下那个存储过程：

 

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

 

举例说明：