SQL Server : find duplicates in a table based on values in a single column

Question

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!

Solution

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

Comments

[slayernoah]

Thanks @EricZ! This is what I was looking for!

[Helen Craigman]

What about using CTE? "with CTE as ( select row_number() over(partition by employeename order by ID ) as rn from table )" - how to continue?

[EricZ]

@HelenCraigman, not sure what you want to do with CTE that query show row number. for string concatenation, FOR XML is the better way.