Reputation: 15807
Please see the DDL below:
create table #test (name1 varchar(100), name2 varchar(100))
insert into #test values ('Marie','ian')
insert into #test values ('Ian','Marie')
and the SQL below:
select count(*) from #test group by name1,name2
This correctly returns to rows:
1,
1
How can I return 1 row:
2
id1 and id2 are the same the other way round so they should be treated as one row. I believe this may be possible with a CHECKSUM but I am unsure.
Upvotes: 0
Views: 25
Reputation: 72175
Try:
SELECT COUNT(*)
FROM #test
GROUP BY IIF(name1 >= name2, name1, name2), IIF(name1 < name2, name1, name2)
With CASE
syntax (SQL Server 2008 R2 or earlier):
SELECT COUNT(*)
FROM #test
GROUP BY CASE WHEN name1 >= name2 THEN name1 ELSE name2 END,
CASE WHEN name1 < name2 THEN name1 ELSE name2 END
This way pairs like ('Bob', 'Martin')
and ('Martin', 'Bob')
are treated the same by the GROUP BY
clause.
Upvotes: 2