Consultas registros duplicados en SQL

[Libras]

Código SQL:

CREATE TABLE #temp (
dato1 INT,
dato2 INT,
campo1 VARCHAR(20),
campo2 VARCHAR(20),
campo3 VARCHAR(20)
)
 
CREATE TABLE #temp3 (
dato1 INT,
dato2 INT,
campo1 VARCHAR(20),
campo2 VARCHAR(20),
campo3 VARCHAR(20)
)
 
 
INSERT INTO #temp VALUES (1,1,'a','b','c')
INSERT INTO #temp VALUES (2,1,'a1','b1','c1')
INSERT INTO #temp VALUES (3,1,'a2','b2','c2')
INSERT INTO #temp VALUES (1,1,'a3','b3','c3')
INSERT INTO #temp VALUES (1,1,'a4','b4','c4')
INSERT INTO #temp VALUES (2,1,'a5','b5','c5')
 
 
SELECT dato1,dato2,IDENTITY(INT,1,1) AS rn INTO #temp2 FROM #temp GROUP BY dato1,dato2
 
 
DECLARE @x INT
DECLARE @y INT
DECLARE @clave VARCHAR(20)
 
SET @x=1
SET @clave=''
while @x<=(SELECT COUNT(*) FROM #temp2)
BEGIN
    SELECT @clave=CONVERT(VARCHAR(20),dato1) + '|' + CONVERT(VARCHAR(20),dato2) FROM #temp2 WHERE rn=@x
    INSERT INTO #temp3
    SELECT top 1 * FROM #temp WHERE CONVERT(VARCHAR(20),dato1) + '|' + CONVERT(VARCHAR(20),dato2) = @clave
    SET @x=@x+1
END
 
SELECT * FROM #temp3
DROP TABLE #temp2
DROP TABLE #temp3
 

eso te regresa :

1 1 a b c
2 1 a1 b1 c1
3 1 a2 b2 c2

que es lo que necesitas no??