这应该可以工作:
WITH Src AS
(
SELECT * FROM (VALUES
(101, 'Tom', 'Dick', 'Harry'),
(102, 'Jack', 'Martin', 'Anna'),
(103, 'Harry', 'Tom', 'Dick'),
(104, 'Dick', 'Tom', 'Harry'),
(105, 'Anna', 'Martin', 'Jack'),
(106, 'Anna', 'Martin', 'Joe')
) T(ID, Player1, Player2, Player3)
), Numbered AS
(
SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) N
FROM
(
SELECT ID,
CONVERT(nvarchar(MAX), Player1) Player1,
CONVERT(nvarchar(MAX), Player2) Player2,
CONVERT(nvarchar(MAX), Player3) Player3
FROM Src
) T1
UNPIVOT
(Name FOR Player IN (Player1, Player2, Player3)) PV
), Grouped AS
(
SELECT N1.ID, N2.ID DuplicateID
FROM Numbered N1
CROSS JOIN Numbered N2
GROUP BY N1.ID,N2.ID
HAVING SUM(CASE WHEN N1.N=N2.N AND N1.ID!=N2.ID AND N1.Name=N2.Name THEN 1 END)=MAX(N1.N)
)
SELECT *
FROM Grouped
WHERE ID<DuplicateID
ID | Player1 | Player2 | Player3
========================== 101 | Tom | Dick | Harry 102 | Jack | Martin | Anna 103 | Harry | Tom | Dick 104 | Dick | Tom | Harry 105 | Dean | Zoya | Mark 106 | Anna | Jack | Martin 107 | Lily | James | Patrick有些输出结果是: 101、103和104是重复的, 以及 102和106是重复的。 - Code Fearer