Hi @leo dec ,
Please also have a try with below:
;with cte as (
select id, room,max(room_cd) max,
sum(case when flag='M' then 1 else 0 end) as mc,
sum(case when flag='F' then 1 else 0 end) as fc
from (select distinct * from #temp) a
group by id, room)
select id, room,
case when max=1 then
case when mc=1 then 'Male' when fc=1 then 'FeMale' else 'Any' end
when max>1 then
case when mc>fc then 'MaleMix' when mc<fc then 'FeMaleMix' else 'Max' end
end room_type
from cte
Output:
id room room_type
106 1 MaleMix
106 2 Male
106 3 FeMale
106 4 FeMaleMix
106 5 Male
106 6 Any
Best regards,
Melissa
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.