Search code examples
sqlsql-servergroupinggroup-concat

Group Employee for each Department

I have a table Details

DeptId        EmpID
-------      ---------
1             1
1             5
1             3
2             8
2             9

I want to group them like this:

DeptId      EmpIDs
-------    -------
 1          1,5,3
 2          8,9

I want this in SQL Server. I know this can be done in MySQL using Group_Concat function. e.g. 

SELECT DeptId, GROUP_CONCAT(EmpId SEPARATOR ',') EmpIDS
FROM Details GROUP BY DeptId

(SQL Fiddle here)

But how to do this with SQL Server? I don't know any function.

Solution

  • One way to simulate GROUP_CONCAT in SQLServer is to use CROSS APPLY and FOR XML PATH()

    select a.[DeptId], SUBSTRING(d.detailsList,1, LEN(d.detailsList) - 1) detailsList
from 
  (
    SELECT DISTINCT [DeptId]
    FROM details
  ) a
CROSS APPLY
  (
    SELECT [EmpID] + ', ' 
    FROM details AS B 
    WHERE A.[DeptId] = B.[DeptId]
    FOR XML PATH('')
  ) D (detailsList)