I need to display yes for col1 if yes is present and no if yes is not present for a id but getting yes in both cases

Question

CREATE TABLE dim(
  "ID"   NUMBER(38,0), 
  "DATE" DATE, 
  "Col1" VARCHAR2(50), 
  "Col2" VARCHAR2(50)
);

INSERT INTO dim (id,col1,col2) VALUES (4160, 'yes', 'no');
INSERT INTO dim (id,col1,col2) VALUES (4160, 'no', 'no');
INSERT INTO dim (id,col1,col2) VALUES (4160, 'no', 'no');
INSERT INTO dim (id,col1,col2) VALUES (4160, 'no', 'no');

Select distinct
       id,
       case
       when count('yes')over(partition by id,col1)>=1
       then 'yes'
       when count('no')over(partition by id,col1)>=1
       and  count('yes')over(partition by id,col1)= 0
       then 'no'
       end as col1,
       case
       when count('yes')over(partition by id,col2)>=1
       then 'yes'
       when count('no')over(partition by id,col2)>=1
       and  count('yes')over(partition by id,col2)= 0
       then 'no'
       end as col2
from   dim 

Output received is:

ID	col1	col2
4160	yes	yes

Final output data should be:

ID	col1	col2
4160	yes	no

Answer 1

Isn't that just a simple MAX function?

Some more sample data:

SQL> select * from dim order by id;

        ID COL1       COL2
---------- ---------- ----------
      1234 no         no          --> 1234: doesn't contain YES at all

      4160 no         no          --> 4160: contains YES in COL1
      4160 no         no
      4160 yes        no
      4160 no         no

      5555 yes        yes         --> 5555: contains YES in both columns

      9999 no         yes         --> 9999: contains YES in both columns
      9999 yes        no

8 rows selected.

Query and the result:

SQL> select id, max(col1) col1, max(col2) col2
  2  from dim
  3  group by id
  4  order by id;

        ID COL1       COL2
---------- ---------- ----------
      1234 no         no
      4160 yes        no
      5555 yes        yes
      9999 yes        yes

SQL>

Answer 2

You can use the COUNT function with conditional aggregation (which will work when you cannot order the data to take advantage of functions like MIN or MAX; for example, if you also had aaa and zzz data in your columns).

Like this:

Select id,
       CASE WHEN COUNT(CASE col1 WHEN 'yes' THEN 1 END) > 0 THEN 'yes' ELSE 'no' END
         AS col1,
       CASE WHEN COUNT(CASE col2 WHEN 'yes' THEN 1 END) > 0 THEN 'yes' ELSE 'no' END
         AS col2
from   dim 
GROUP BY id

Which, for the sample data outputs:

ID	COL1	COL2
4160	yes	no

db<>fiddle here