sql - 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

question from:https://stackoverflow.com/questions/10952696/how-to-do-a-case-sensitive-group-by

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