How to Select Distinct Group By With Exclusions

Question

I have a large table similar to:

The goal:

1. For each UserID with a Y in the Access column, show UserID only as "Y" (exclude "N", "?")
2. For each UserID without any Y's in the Access column, show UserID only as "N" (exclude "?")
3. For each UserID with only ? in the Access column, show UserID only as "?"

Desired result:

What's the correct way to query the dataset for this result?

Edit: the best query I could come up with looks something like:

    select distinct UserID, Access
    from table
    where Access = 'Y'
    group by UserID, Access
UNION

select distinct UserID, Access
from table
where Access = 'N'
and UserID not in (select distinct UserID from table where Access = 'Y')
group by UserID, Access

UNION

select distinct UserID, Access
from table
where Access = '?'
and UserID not in (select distinct UserID from table where Access = 'Y' or Access = 'N')
group by UserID, Access
order by UserID, Access

Answer 1

Sounds like you just need conditional aggregation

SELECT
  t.UserID,
  CASE WHEN COUNT(CASE WHEN t.Access = 'Y' THEN 1 END) > 0
         THEN 'Y'
       WHEN COUNT(CASE WHEN t.Access = 'N' THEN 1 END) > 0
         THEN 'N'
       ELSE   '?'
    END AS Access
FROM [table] t
GROUP BY
  t.UserID;

db<>fiddle