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!

There must be at least 100 duplicates of this question (but I, too, am too lazy to search for them). — Aaron Bertrand
– Aaron Bertrand, Commented May 13, 2013 at 15:51

EricZ · Accepted Answer · 2013-05-13 15:02:03Z

6

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

answered May 13, 2013 at 15:02

EricZ

6,2151 gold badge33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Helen Craigman Over a year ago

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 Over a year ago

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

granadaCoder · Accepted Answer · 2013-05-13 15:04:19Z

0

Here is a Northwind example:

Use Northwind
GO

SELECT
   ord1.CustomerID,
   OrderIdList = substring((SELECT ( ', ' + convert(varchar(16) , OrderID) )
                           FROM [dbo].[Orders] ord2
                           WHERE ord1.CustomerID = ord2.CustomerID
                           ORDER BY 
                              CustomerID,
                              OrderID
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM [dbo].[Orders] ord1
GROUP BY CustomerID

answered May 13, 2013 at 15:04

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related