How to use only one query (no sub query) to get the rows which mach the requirement?

I need some suggestions for my case. First, I have a table (T1) like this:

         Date   ID  Flag 
   2020-08-05    a    0    
   2020-09-05    a    0
   2020-10-05    a    1
   2020-11-05    a    1 
   2020-12-05    a    0
   2020-08-05    b    1    
   2020-09-05    b    1
   2020-10-05    b    1
   2020-11-05    b    1 
   2020-12-05    b    1
   2020-08-05    c    0    
   2020-09-05    c    0
   2020-10-05    c    0
   2020-11-05    c    0 
   2020-12-05    c    0

I want to have a table like this:

         Date   ID  Flag  Result
   2020-08-05    a    0   (null)
   2020-09-05    a    0   (null)
   2020-10-05    a    1   (null)
   2020-11-05    a    1   (null)
   2020-12-05    a    0   (null)
   2020-08-05    b    1     T
   2020-09-05    b    1     T
   2020-10-05    b    1     T
   2020-11-05    b    1     T
   2020-12-05    b    1     T
   2020-08-05    c    0     F
   2020-09-05    c    0     F 
   2020-10-05    c    0     F 
   2020-11-05    c    0     F
   2020-12-05    c    0     F

The issue is, if I can use only one query (without any sub query), what I can do? I tried like this:

Select * case
when Flag = 0 then ‘F’
when Flag = 1 then ‘T’ End as Result From T1;

But for the ID = a, if i used this query, the result is not right.
I know, i use ‘With’ or something else with a sub query (one moere ‘select..’), the problem can be easily solved. So, what if i can use only one ‘select…’, any suggestions?

Answer

I think you want window functions to compare all the values for a given id. The following returns 0/1 (which makes sense to me:

select t.*,
       (case when min(flag) over (partition by id) = max(flag) over (partition by id)
             then min(flag) over (partition by id)
        end) as result
from t;

For Y/N:

select t.*,
       (case when min(flag) over (partition by id) = 1 and
                  max(flag) over (partition by id) = 1
             then 'Y'
             when min(flag) over (partition by id) = 0 and
                  max(flag) over (partition by id) = 0
             then 'N'                 
        end) as result
from t;

Note: This assumes that flag is never NULL, which is consistent with your data.