How to insert a value from another row into a column - SQL Server

Question

I'm working on SQL for a project, I need to update Soh_Wh_A and Soh_Wh_B based on some rules.

This is table_A:

| Code  | Warehouse | StockOnHand | Wh_A     | Wh_B 
----------------------------------------------------
| 001   | A         | 10          | NULL     | NULL     
| 001   | B         | 20          | NULL     | NULL     
| 003   | A         | 30          | NULL     | NULL     
| 033   | B         | 40          | NULL     | NULL     

I want to populate columns Wh_A and Wh_B. For example, lets work on the first row, Wh_A should have the same value of the column StockOnHand as this row belongs to warehouse "A". That is easy to do using an update case when statement.

What is difficult for me is to populate the column Wh_B with the column StockOnHand from the second row.

The table should be like this at the end.

| Code  | Warehouse | StockOnHand | Wh_A     | Wh_B 
----------------------------------------------------
| 001   | A         | 10          | 10       | 20
| 001   | B         | 20          | 10       | 20
| 003   | A         | 30          | 30       | 40     
| 033   | B         | 40          | 30       | 40     

This is what I have done so far...

update Table_A set
Wh_A = (case 
        when warehouse = 'A' then stockOnHand
        when warehouse = 'B' then ... end)
Wh_B = (case 
        when warehouse = 'A' then stockOnHand
        when warehouse = 'B' then ... end)

Answer 1

You can use window functions:

select 
    code,
    warehouse,
    stockOnHand,
    max(case when warehouse = 'A' then stockOnHand end)
        over(partition by code) wh_a,
    max(case when warehouse = 'B' then stockOnHand end)
        over(partition by code) wh_b
from table_a

It is easy to turn this to an update query using an updateable cte:

with cte as (
    select 
        wh_a,
        wh_b
        max(case when warehouse = 'A' then stockOnHand end)
            over(partition by code) new_wh_a,
        max(case when warehouse = 'B' then stockOnHand end)
            over(partition by code) new_wh_b
    from table_a
)
update cte set wh_a = new_wh_a, wh_b = new_wh_b