sql-server - 基于公司 ID 的逗号分隔名称

Question

我有下面的员工详细信息表

EmployeeName   CompayId    CompanyLastActive
---------------------------------------------------------
robort          112        10 Jun 2015 09:30 
john            113        11 Jun 2015 11:10
sunny           114        14 Jun 2015 16:10
sumanth         114        15 Jun 2015 18:11
usha            115        07 Jun 2015 13:14
sudheer         115        14 Jun 2015 17:10
sweety          115        08 Jun 2015 16:34

我需要根据 CompanyID 以逗号分隔的 EmployeeName 获取最新的员工事件时间，如下所示

EmployeeName           CompayId        CompanyLastActive
---------------------------------------------------------
robort                 112               10 Jun 2015 09:30 
john                   113               11 Jun 2015 11:10
sunny, sumanth         114               15 Jun 2015 18:11        
usha, sudheer, sweety   115               14 Jun 2015 17:10

请帮我解决一下。

Answer 1

SELECT EmployeeName = STUFF((
          SELECT ',' + e1.EmployeeName
          FROM dbo.Employee e1
          WHERE e.CompayId = e1.CompayId
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),
        e.CompayId,
        MAX(CompanyLastActive) as CompanyLastActive
FROM dbo.Employee e
GROUP BY e.CompayID
ORDER BY e.CompayId

结果:

EmployeeName        CompayId    CompanyLastActive
-------------------------------------------------------
robort              112         June, 10 2015 09:30:00
john                113         June, 11 2015 11:10:00
sunny,sumanth       114         June, 15 2015 18:11:00
usha,sudheer,sweety 115         June, 14 2015 17:10:00

SQL Fiddle 中的示例结果.