如果我没记错的话，你可以使用窗口函数和 case 表达式：

select
    id,
    case when max(case when test_result = 'Positive' then 1 else 0 end) over(partition by id) = 1
        then 'Positive'
        else testResult
    end testResult
from mytable

case&window函数的答案很好。。。但是这里有一个使用子查询的选项。

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData

SELECT * INTO #TestData FROM (VALUES
    (1, 'Positive'),
    (2, 'Positive'),
    (2, 'Negative'),
    (3, 'Unknown'),
    (3, 'Negative'),
    (3, 'Positive'),
    (4, 'Pending'),
    (4, 'Positive'),
    (5, 'Unknown'),
    (6, 'Negative'),
    (7, 'Unknown'),
    (7, 'Positive'),
    (8, 'Positive')
) S(ID, TestResult)

-- Display Test Result Data
SELECT ID, TestResult FROM #TestData

-- Display Query Proposal
SELECT
    ID,
    CASE WHEN EXISTS(SELECT * FROM #TestData T2 WHERE T2.ID = T1.ID AND T2.TestResult = 'Positive') THEN 'Positive' ELSE T1.TestResult END
FROM
    #TestData T1