As a starter: a SQL table represents an unordered set of rows; there is no inherent ordering. Assuming that you have a column that defines the ordering of the rows, say id , and that your values are stored in column col , you can use lead() and a case expression as follows:

select col,
    case when col = lead(col, 1, col) over(order by id) 
        then 'Fail' else 'OK' 
    end as status
from mytable t

Assuming you have some sort of column that you can use to determine the row order then you can use the LEAD window function to get the next value.

SELECT 
  [A],
  CASE 
    WHEN [A] = LEAD([A], 1, [A]) OVER (ORDER BY SomeSortIndex) THEN 'Fail' 
    ELSE 'Ok' 
  END [B]
FROM src

The additional parameters in the LEAD function specify the row offset and default value in case there is no additional row. By using the current value as the default it will cause the condition to be true and display Fail like the last result in your example.

Check out the lead() and lag() functions.
They do work best (most reliable) with a sorting field... Your sample does not appear to contain such a field. I added a sorting field in my second solution.
The coalesce() function handles the first row that does not have a preceeding row.

Solution 1 without sort field

create table data
(
  A nvarchar(10)
);

insert into data (A) values
('BEM'),
('Compliance'),
('BEM'),
('Compliance'),
('BEM'),
('Compliance'),
('Compliance'),
('Compliance');

select d.A,
       coalesce(lag(d.A) over(order by (select null)), '') as lag_A,
       case
         when d.A <> coalesce(lag(d.A) over(order by (select null)), '')
         then 'Ok'
         else 'Fail'
       end as B
from data d;

Solution 2 with sort field

create table data2
(
  Sort int,
  A nvarchar(10)
);

insert into data2 (Sort, A) values
(1, 'BEM'),
(2, 'Compliance'),
(3, 'BEM'),
(4, 'Compliance'),
(5, 'BEM'),
(6, 'Compliance'),
(7, 'Compliance'),
(8, 'Compliance');

select d.A,
       case
         when d.A <> coalesce(lag(d.A) over(order by d.Sort), '')
         then 'Ok'
         else 'Fail'
       end as B
from data2 d
order by d.Sort;

Fiddle with results.