How to do a case sensitive GROUP BY?

group-by, sql, sql-server, sql-server-2008

Solution

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

Problem

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

Original source