Populating missing rows using SELECT statement

Question

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:

ProductID	Status
AP56546
AP56546	Shipped
AP56546
AD92402	Delayed
AD92402
AD92402
BE91455
BE91455
SL19423
SL19423
SL19423
SL19423	Expected

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)

ProductID	Status
AP56546	Shipped
AP56546	Shipped
AP56546	Shipped
AD92402	Delayed
AD92402	Delayed
AD92402	Delayed
BE91455
BE91455
SL19423	Expected
SL19423	Expected
SL19423	Expected
SL19423	Expected

Thank you

What's the expected result if a ProductID has different Status values? — jarlh
– jarlh, Commented Feb 2, 2023 at 12:34

Stu · Accepted Answer · 2023-02-02 11:42:01Z

2

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

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

answered Feb 2, 2023 at 11:42

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mukesh Arora · Accepted Answer · 2023-02-02 11:34:58Z

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;

Collectives™ on Stack Overflow

Populating missing rows using SELECT statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related