CODEWITHSUNDEEP
sqlsql-server-2012nulluniqueidentifier
Ryan
Ryan

Reputation: 87

How to assign a value to a null based on a unique identifier in SQL?

I'm trying to assign a string to a null category based on the ID column. What I want to do is replace the nulls with the sales rep based on ID. I don't necessarily need to overwrite the nulls, adding a column with the adjusted sales names works too. I was thinking maybe using a case group by based on the unique ID but I have had no luck. I have a table with this type of structure:

Sample Data:

ID            SalesRep      Amount  
123           Steve         50.00  
123           Steve         100.00  
123           Steve         150.00   
123           NULL          200.00

456           Dave          25.00  
456           Dave          75.00  
456           Dave          500.00   
456           NULL          300.00

Expected Result:

ID            SalesRep      Amount  
123           Steve         50.00  
123           Steve         100.00  
123           Steve         150.00   
123           Steve         200.00

456           Dave          25.00  
456           Dave          75.00  
456           Dave          500.00   
456           Dave          300.00

Upvotes: 0

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use window functions:

select t.*,
       coalesce(SalesRep,
                max(SalesRep) over (partition by id) as imputed_ SalesRep
               )
from t;

Upvotes: 1

Related Questions