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

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

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