Not sure about the performance but tried doing it following way... please consider cte and cte1 as tables wrt to update statement written at the end

;with cte as

(

select 1 as pid, null as id1, null as id2, null as id3

union all

select 2 as pid, null as id1, null as id2, null as id3

),

cte1 as

(

select 1 as pid,1 as id,10 as value

union all

select 1 as pid,2 as id,20 as value

union all

select 1 as pid,3 as id,30 as value

union all

select 2 as pid,1 as id,10 as value

union all

select 2 as pid,2 as id,20 as value

union all

select 2 as pid,3 as id,30 as value

),

cte2 as

(

select distinct pid,

(select value from cte1 c1 where c1.pid = c2.pid and c1.id = 1 ) as id1,

(select value from cte1 c1 where c1.pid = c2.pid and c1.id = 2 ) as id2,

(select value from cte1 c1 where c1.pid = c2.pid and c1.id = 3 ) as id3 from cte1 c2

)

update cte

set id1 = c2.id1,id2 = c2.id2,id3 = c2.id3

from cte2 c2 inner join cte c on c2.pid = c.pid