w0051977
w0051977

Reputation: 15807

Grouping taking into account field names vice versa

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

Answers (1)

Giorgos Betsos
Giorgos Betsos

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

Related Questions