Calculate the number of times that the two columns are equal to each other and remove duplicates.

-- Calculate the number of times that the two columns are equal to each other and remove duplicates.

If object_id ('[TB]') is not null drop table [TB]
Go
Create Table [TB] ([num1] varchar (10), [num2] varchar (10 ))
Insert [TB]
Select 'A', 'B' Union all
Select 'B', 'C' Union all
Select 'B', 'A' Union all
Select 'C', 'B' Union all
Select 'C', 'D' Union all

Select * from TB

Select T. num1, T. num2, count (*) as repeated
From (
Select num1, num2 from TB
Union all
Select num2, num1 from TB
) T
Where T. num1 <t. num2
Group by T. num1, T. num2

/*

Num1 num2
A B
B c
B
C B
C d

----------------------------

Num1 num2 (repeated)
A B 2
B C 2
C d 1

*/