Have a look at example:
DECLARE @tmp TABLE(Acc VARCHAR(30), [Plan] VARCHAR(30))
INSERT INTO @tmp (Acc, [Plan])
VALUES('121', 'A'), ('121', 'A'),
('121', 'B'), ('131', 'A'),
('131', 'B'), ('141', 'C'),
('141', 'C'), ('145', 'D'),
('190', 'R'), ('200', 'S')
SELECT Acc, CASE WHEN ROW_NUMBER() OVER(PARTITION BY Acc, [Plan] ORDER BY [Plan])>1 THEN NULL ELSE [Plan] END AS [Plan]
FROM @tmp
UPDATE t1 SET t1.[Plan] = NULL
FROM (
SELECT Acc, [Plan], ROW_NUMBER() OVER(PARTITION BY Acc, [Plan] ORDER BY [Plan]) As RowNo
FROM @tmp
) AS t1
WHERE t1.RowNo > 1
SELECT *
FROM @tmp
Result:
Acc Plan
121 A
121 NULL
121 B
131 A
131 B
141 C
141 NULL
145 D
190 R
200 S
For further details, please see:
Ranking Functions (Transact-SQL)[
^]