Distinct few Columns and check condition on the column not included in distinct columns

Table “Module” has these columns

How to select distinct columns of ModuleGroupDisplayAs and ModuleGroup And After that, Add column on checking these distinct columns has particular UserID and CompanyID. That means I want to check condition that distinct columns has this particular userID and CompanyID. The expected result would be as following.

Answer

It looks like you just need to group by the last two columns and use a case expression to determine yes/no, but want Yes to override No to remove duplicates, for which you can use aggregations

select  
    Max(case when UserID='agnes' and CompanyID='aud' then 'Yes' else 'No' end) as [Include?],
    ModuleGroupDisplayAs, ModuleGroup
from Module
group by ModuleGroupDisplayAs, ModuleGroup