选择
 employeename，
 ID = STUFF（（SELECT'，'+ CAST（e 2. [ID] AS VARCHAR（10））
 FROM emp e2 
 WHERE e2.employeename = e1.employeename 
对于XML PATH（''）
），1,1 ，''）
 FROM emp e1 
 GROUP BY employeename有COUNT（*）> 1 
 

SQL Fiddler

I have a SQL Server table with the following fields and sample data:

ID   employeename
1    Jane
2    Peter
3    David
4    Jane
5    Peter
6    Jane

The ID column has unique values for each row.

The employeename column has duplicates.

I want to be able to find duplicates based on the employeename column and list the IDs of the duplicates next to them separated by commas.

Output expected for above sample data:

employeename   IDs
Jane           1,4,6
Peter          2,5

There are other columns in the table that I do no want to consider for this query.

Thanks for all your help!

select
 employeename,
 IDs = STUFF((SELECT ','+ CAST(e2.[ID] AS VARCHAR(10)) 
  FROM emp e2
  WHERE e2.employeename = e1.employeename
  For XML PATH('')
 ),1,1,'')
FROM emp e1
GROUP BY employeename having COUNT(*) > 1

SQL Fiddler