How to do a case sensitive GROUP BY?

Question

If I execute the code below:

with temp as (   select 'Test' as name   UNION ALL   select 'TEST'   UNION ALL   select 'test'   UNION ALL   select 'tester'   UNION ALL   select 'tester' ) SELECT name, COUNT(name) FROM temp group by name 

It returns the results:

TEST   3 tester 2 

Is there a way to have the group by be case sensitive so that the results would be:

Test   1 TEST   1 test   1 tester 2 

Answer 1

You need to cast the text as binary (or use a case-sensitive collation).

With temp as (   select 'Test' as name   UNION ALL   select 'TEST'   UNION ALL   select 'test'   UNION ALL   select 'tester'   UNION ALL   select 'tester' ) Select Name, COUNT(name) From temp Group By Name, Cast(name As varbinary(100)) 

Using a collation:

Select Name Collate SQL_Latin1_General_CP1_CS_AS, COUNT(name) From temp Group By Name Collate SQL_Latin1_General_CP1_CS_AS 

Answer 2

You can use an case sensitive collation:

with temp as (   select 'Test' COLLATE Latin1_General_CS_AS as name   UNION ALL   select 'TEST'   UNION ALL   select 'test'   UNION ALL   select 'tester'   UNION ALL   select 'tester' ) SELECT name, COUNT(name) FROM temp group by name