select a.*,d.bname,d.cname from @a a

left join

(select b.code b_code,b.bname,c.code c_code,c.cname from

(select ROW_NUMBER() over(partition by code order by code)Rank

,* from @b-2)b full join

(select ROW_NUMBER() over(partition by code order...