下面使用按ProductId分区的max的代码应该可以满足您的需要：

select ProductId, Max(Status) over(partition by ProductId) Status
from t;

您可以使用以下查询获得所需的结果：

样表

CREATE TABLE TableData (
    ProductID varchar(10),
    Status varchar(10)
)

示例数据

INSERT INTO TableData (ProductID, Status)
VALUES
    ('AP56546', ''),
    ('AP56546', 'Shipped'),
    ('AP56546', ''),
    ('AD92402', 'Delayed'),
    ('AD92402', ''),
    ('AD92402', ''),
    ('BE91455', ''),
    ('BE91455', ''),
    ('SL19423', ''),
    ('SL19423', ''),
    ('SL19423', ''),
    ('SL19423', 'Expected');

查询

WITH CTE AS (
 SELECT ProductID, Status 
  FROM TableData
  WHERE Isnull(Status,'') <> ''
  GROUP BY ProductID, Status
)
SELECT TableData.ProductID, COALESCE(CTE.Status, '') AS Status
FROM TableData
LEFT JOIN CTE
ON TableData.ProductID = CTE.ProductID;