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

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

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

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

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

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

 

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

 

接下来,再修改一下那个存储过程:

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

 

举例说明:

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