SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE rates (ID int, Type varchar(10), Rate decimal(19, 2));

INSERT INTO rates (ID, Type, Rate)
VALUES
  (111, 'OP', 123.45)
, (222, 'OP', 123.45)
, (222, 'OPS', 234.56)
, (333, 'OPS', 234.56)
;
4 rows affected

/* troublemakers */
/*
INSERT INTO rates (ID, Type, Rate)
VALUES
  (444, 'OP', 123.45)
, (444, 'OP', 999.99)
, (666, 'OPS', 234.56)
, (666, 'OPS', 234.56)
, (666, 'OPS', 999.99)
, (777, 'XY', 999.99);
*/
SELECT ID, Type, Rate 
FROM rates 
WHERE Type = 'OP'  
  
UNION ALL  

SELECT ID, Type, Rate 
FROM rates 
WHERE Type <> 'OP' 
AND ID NOT IN (SELECT ID FROM rates WHERE Type = 'OP');


ID
Type
Rate


111
OP
123.45

222
OP
123.45

333
OPS
234.56


SELECT DISTINCT
    ISNULL(T1.ID, T2.ID) AS ID, 
    IIF(T1.Type IS NOT NULL AND T2.Type IS NOT NULL, T1.Type, ISNULL(T2.Type, T1.Type)) AS Type,
    Rate =
        CASE
            WHEN T1.Type IS NOT NULL AND T2.Type IS NOT NULL THEN T1.Rate
            ELSE ISNULL(T2.Rate, T1.Rate)
        END
FROM rates AS T1
FULL JOIN rates AS T2
    ON T1.ID = T2.ID -- Same ID
    AND T1.Type <> T2.Type -- Different Types (only matches if there's more than 1 Type)
    AND T1.Type = 'OP' -- The left side of the FULL JOIN has to be of Type = OP


ID
Type
Rate


111
OP
123.45

222
OP
123.45

222
OPS
234.56

333
OPS
234.56