How to compare multiple values of column in sql server

Is this SQL Server?
If so, I think it can be solved neatly using a Common Table Expression (CTE)...
we create a temporary table with your sample data:

create table #tmp (prid int, isroi varchar(1));

insert into #tmp select 12,'y';
insert into #tmp select 12,'N';
insert into #tmp select 13,'n';
insert into #tmp select 14,'y';
insert into #tmp select 15,'n';
insert into #tmp select 15,'n';

select * from #tmp;

we know we want a recordset which collects all the "prid" where a 'y' value for "isroi" is present:

select distinct prid,isroi from #tmp where isroi = 'y'

We put this query in a CommonTableExpression, and LEFT OUTER JOIN it to a SELECT * FROM the original table, using ISNULL(,) to replace the missing values with an 'n'

with CTEYesValues as (select distinct prid,isroi from #tmp where isroi = 'y')

select #tmp.*, isnull(CTEYesValues.isroi, 'n') as new_roi from #tmp left outer join CTEYesValues on CTEYesValues.prid = #tmp.prid;

The output:

prid	isroi	new_roi
12	y	y
12	N	y
13	n	n
14	y	y
15	n	n
15	n	n

ok here it is my solution

select prid,isroi, max(isroi) over(partition by prid) as new_roi
from tmp

if you have only y and n values in isroi column
then my solution will perfectly work for you

My Fiddle solution

try this

select prid,new_roi=case when isroi='y' then 'y' when isroi='N' then 'y' else '' end from table_name;