I am stuck at the following issue writing a SELECT SQL statement and was wondering if anyone can help. I have the following table with ProductID and Status fields:

How can I populate the Status for all Product with the same ID if even one of them has a status against it? If no Status exists for a certain product, then it can stay null. How can I do this using a SELECT statement? The expected result should be below (ProductID BE91455 is still null because there is no status against any of the rows where it occurs)

Thank you

CodePudding user response：

The following using max partitioned by ProductId should work for you:

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

CodePudding user response：

You can achieve desired results using below query:

Sample Table

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

Sample Data

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

Query

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;