SQL Server:根据单个列中的值查找表中的重复项。

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

我有一个SQL Server表，其中包含以下字段和示例数据:

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

The ID column has unique values for each row.

ID列对每一行都有惟一的值。

The employeename column has duplicates.

employeename列有副本。

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.

我希望能够基于employeename列找到副本，并列出在它们旁边用逗号分隔的副本的id。

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!

谢谢你的帮助!

2 个解决方案

#1

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

SQL提琴手

#2

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

#1

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