Remove Cross duplicate rows in SQL Server

Hello friends, I got one question from my friend who recently appeared for an interview, that how to remove cross duplicate rows in SQL.? So I was looking for a solution but after many failures I got correct solution. Let you explain the question and solution.

Following is my table:-

Now I want Output like following:

Using following easy and fast query you will get desired output:

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(ID1 INT, ID2 INT)
GO
INSERT INTO #TEMP
VALUES (2,1)
, (2,1)
, (1,2)
, (3,1)
, (5,2)
, (2,5)
, (4,3)
, (3,4)
, (6,2)
, (2,6)
, (2,7)
 
select * from #TEMP

select Id1,Id2 from(
SELECT ROW_NUMBER() OVER (PARTITION BY (A.ID1 * A.ID2) + (A.ID1 + A.ID2) ORDER BY A.ID1,A.ID2) AS ID, * FROM #TEMP A)as B
where id = 1

You can also achieve this using WITH statement. I also got solution using WITH statement, but that will not as fast as above query.

You guys can try by your own and comment it. I would like to see more response from you.

Thanks 🙂