SqlServer 对分组的内容进行拼接-group_concat

SqlServer 对分组的内容进行拼接：

方案1：xml 子集,性能较差

方案2：借助 sqlCLR 接入。实现group_concat。性能完美，但是 阿里云的不支持！！！！

CREATE TABLE [dbo].[a_data_test]( [empid] [INT] NULL, [workdate] [DATETIME] NULL ) ON [PRIMARY]

INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (1, 2018-8-17 10:20:43.33310:); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (1, 2018-8-17 10:20:43.333); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (2, 2018-8-17 10:21:09.29310:); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (2, 2018-8-17 10:21:09.293); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (3, 2018-8-17 10:21:37.87710:); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (3, 2018-8-17 10:21:37.880); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (3, 2018-8-17 10:21:37.88010:); GO

1:

SELECT
ta.empid,
 STUFF((SELECT , +CONVERT(varchar(100), b.workdate, 25) 
                         FROM a_data_test b
                         WHERE ta.empid=b.empid
                         FOR XML PATH(), TYPE).value(., NVARCHAR(MAX)), 1, 1, )
FROM 
(
SELECT 
a.empid
 FROM
dbo.a_data_test a
GROUP BY a.empid
) ta
--LEFT JOIN dbo.a_data_test b ON b.empid = ta.empid

SELECT ta.empid, STUFF((SELECT , +CONVERT(varchar(100), b.workdate, 25) FROM a_data_test b WHERE ta.empid=b.empid FOR XML PATH(), TYPE).value(., NVARCHAR(MAX)), 1, 1, ) FROM ( SELECT a.empid FROM dbo.a_data_test a GROUP BY a.empid ) ta --LEFT JOIN dbo.a_data_test b ON b.empid = ta.empid

2：

 https://archive.codeplex.com/?p=groupconcat
https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr/tree/master/GroupConcat  https://archive.codeplex.com/?p=groupconcat https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr/tree/master/GroupConcat

https://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server https://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server

https://www.anexinet.com/blog/group_concat-functionality-for-sql-server/ https://www.anexinet.com/blog/group_concat-functionality-for-sql-server/

SqlServer 对分组的内容进行拼接： 方案1：xml 子集,性能较差 方案2：借助 sqlCLR 接入。实现group_concat。性能完美，但是 阿里云的不支持！！！！ CREATE TABLE [dbo].[a_data_test]( [empid] [INT] NULL, [workdate] [DATETIME] NULL ) ON [PRIMARY] INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (1, 2018-8-17 10:20:43.33310:); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (1, 2018-8-17 10:20:43.333); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (2, 2018-8-17 10:21:09.29310:); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (2, 2018-8-17 10:21:09.293); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (3, 2018-8-17 10:21:37.87710:); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (3, 2018-8-17 10:21:37.880); GO INSERT INTO [a_data_test] ([empid], [workdate]) VALUES (3, 2018-8-17 10:21:37.88010:); GO 1: SELECT ta.empid, STUFF((SELECT , +CONVERT(varchar(100), b.workdate, 25) FROM a_data_test b WHERE ta.empid=b.empid FOR XML PATH(), TYPE).value(., NVARCHAR(MAX)), 1, 1, ) FROM ( SELECT a.empid FROM dbo.a_data_test a GROUP BY a.empid ) ta --LEFT JOIN dbo.a_data_test b ON b.empid = ta.empid 2： https://archive.codeplex.com/?p=groupconcat https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr/tree/master/GroupConcat https://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server https://www.anexinet.com/blog/group_concat-functionality-for-sql-server/