How to effectively count pairs

[CNIDog]

I need to create a table to store matched pairs that I can query and get a count of how many times a particular matchup was entered. For example "5" might be paired with "6", but "6" might be also paired with "5". (Quotes are for clarity - not to designate strings.) I do not want to differentiate between the two, but have a query return a result of (2) for "5" and "6".

I can create a table with match_1 and match_2 and always force the lower number into match_1, but that seems like a hokey solution to me. Adding them together does not work because "4" and "7" produce the same sum.

I would appreciate any suggestions on what might be the best table structure for recording such entries and a solution for effectively querying for the results.

[sknake]

Please post the table(s) and their structures that you are dealing with. I don't really follow you on how the values are paired. Do you have a single table with Col1/Col2 that are integers and you want to see how many times a number pair appears in that table regardless of which column its in?

[CNIDog]

Please post the table(s) and their structures that you are dealing with. I don't really follow you on how the values are paired. Do you have a single table with Col1/Col2 that are integers and you want to see how many times a number pair appears in that table regardless of which column its in?

I am doing a fresh installation of my OS and cannot send the tables, but yes I have a single table with two columns and I would like to count number pairs exactly as you explained.

[sknake]

IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL DROP TABLE #Temp
Create Table #Temp
(
  Col1 int,
  Col2 int,
)

Insert Into #Temp (Col1, Col2) Values (1, 1)
Insert Into #Temp (Col1, Col2) Values (2, 1)
Insert Into #Temp (Col1, Col2) Values (3, 1)
Insert Into #Temp (Col1, Col2) Values (4, 1)
Insert Into #Temp (Col1, Col2) Values (5, 1)
Insert Into #Temp (Col1, Col2) Values (6, 1)
Insert Into #Temp (Col1, Col2) Values (7, 1)
Insert Into #Temp (Col1, Col2) Values (8, 1)
Insert Into #Temp (Col1, Col2) Values (1, 1)
Insert Into #Temp (Col1, Col2) Values (2, 1)
Insert Into #Temp (Col1, Col2) Values (3, 1)
Insert Into #Temp (Col1, Col2) Values (4, 1)
Insert Into #Temp (Col1, Col2) Values (5, 1)
Insert Into #Temp (Col1, Col2) Values (6, 1)
Insert Into #Temp (Col1, Col2) Values (7, 1)
Insert Into #Temp (Col1, Col2) Values (8, 1)

Insert Into #Temp (Col1, Col2) Values (1, 1)
Insert Into #Temp (Col1, Col2) Values (1, 2)
Insert Into #Temp (Col1, Col2) Values (1, 3)
Insert Into #Temp (Col1, Col2) Values (1, 4)
Insert Into #Temp (Col1, Col2) Values (1, 5)
Insert Into #Temp (Col1, Col2) Values (1, 6)
Insert Into #Temp (Col1, Col2) Values (1, 7)
Insert Into #Temp (Col1, Col2) Values (1, 8)


Insert Into #Temp (Col1, Col2) Values (1, 3)
Insert Into #Temp (Col1, Col2) Values (1, 4)

Insert Into #Temp (Col1, Col2) Values (7, 1)
Insert Into #Temp (Col1, Col2) Values (8, 1)


Select 
(Case When Col1 <= Col2 Then Col1 Else Col2 End) As N1,
(Case When Col1 >= Col2 Then Col1 Else Col2 End) As N2,
Count(*) As Cnt
From #Temp
Group By 
(Case When Col1 <= Col2 Then Col1 Else Col2 End),
(Case When Col1 >= Col2 Then Col1 Else Col2 End)

[CNIDog]

That is going to work just fine. Thank you.