sql - Using GROUP BY clause to replace dissimilar rows with single value -

i trying (psuedo - of course):

select    city,   case when count( [group] ) > 1 'multiple' else [group] end  type     offices group   city 

where offices contains rows like:

id |   group   |  city ---------------------- 1  |    'a'    | 'houston' 2  |    'b'    | 'houston' 3  |    'c'    | 'houston' 4  |    's'    | 'boston' 5  |    'r'    | 'detroit' 

and result like:

   city   | group --------------------  'houston'| 'multiple'  'boston' |   's'  'detroit'|   'r' 

i know can do:

select    city,   case when count([group]) > 1        'multiple'   else         ( select [group] test t.city = city )   end cgroup      test t group     city 

i thought should simpler.
without sub query?

you can find min , max of column , act if not identical:

declare @t table (id int not null,[group] char(1) not null,city varchar(20) not null) insert @t(id,[group],city) values (1,'a','houston'), (2,'b','houston'), (3,'c','houston'), (4,'s','boston' ), (5,'r','detroit')  select city,    case       when min([group]) != max([group]) 'multiple'       else max([group]) end [group] @t group city 

the server should smart enough run max aggregate once despite appearing twice in select clause.

result:

city                 group -------------------- -------- boston               s detroit              r houston              multiple