Ilmu Sql

Saya punya table #x yang isinya seperti berikut

Saya ingin mengganti hasilnya dengan data seperti berikut:




berikut querynya

select * from (
    select idstructure, idcovjob1, (select top 1 idcovjob from #x where idstructure=a.idstructure and idcovjob<>a.idcovjob1) idcovjob2
    from (
        select distinct idstructure, (select top 1 idcovjob from #x where idstructure=a.idstructure) idcovjob1 from #x a
    ) a
   
    union
   
    select b.idstructure, idcovjob1, c.idcovjob idcovjob2 from (
        select idstructure, idcovjob1, (select top 1 idcovjob from #x where idstructure=a.idstructure and idcovjob<>a.idcovjob1) idcovjob2
        from (
            select distinct idstructure, (select top 1 idcovjob from #x where idstructure=a.idstructure) idcovjob1 from #x a
        ) a
    ) b
    inner join #x c on (b.idstructure=c.idstructure and idcovjob<>b.idcovjob1 and idcovjob<>b.idcovjob2)
) a
order by 1