How to order columns results in another column (SQL Server)

I have this table

RowCnt  Lvl a_TargetID  b_TargetID a_LOG a_RF a_GNB a_SVC  a_1st  a_2nd  a_3rd  a_4th
1000    0   100         102        95    83   98    30     NULL   NULL   NULL   NULL
1000    0   100         103        67    84   28    99     NULL   NULL   NULL   NULL
1000    0   100         104        74    88   94    57     NULL   NULL   NULL   NULL
1500    3   1           33         75    26   93    85     NULL   NULL   NULL   NULL
1000    0   100         34         98    55   34    33     NULL   NULL   NULL   NULL
1000    0   100         35         47    45   45    46     NULL   NULL   NULL   NULL

The key columns in my table are RowCnt , Lvl , a_TargetID , b_TargetID

I want to put the name of the column as the order of their value in the a_1st a_2nd a_3rd a_4th

so the final results will be like this

RowCnt  Lvl a_TargetID  b_TargetID a_LOG a_RF a_GNB a_SVC  a_1st  a_2nd  a_3rd  a_4th
1000    0   100         102        95    83   98    30     a_GNB   a_LOG   a_RF   a_SVC
1000    0   100         103        67    84   28    99     a_SVC   a_RF   a_LOG   a_GNB
1000    0   100         104        74    88   94    57     a_GNB   a_RF   a_LOG   a_SVC
1500    3   1           33         75    26   93    85     a_GNB   a_SVC   a_LOG   a_RF
1000    0   100         34         98    55   34    33     a_LOG   a_RF   a_GNB   a_SVC
1000    0   100         35         47    45   45    46     a_LOG   a_SVC   a_RF   a_GNB

I can do this but it seems very wrong

UPDATE Targets
SET a_1st = CASE WHEN a_LOG >= a_RF and a_LOG >= a_GNB and a_LOG >= a_SVC THEN 'a_LOG'
WHEN a_RF >= a_LOG and a_RF >= a_GNB and a_RF >= a_SVC THEN 'a_RF'
WHEN a_GNB>= a_LOG and a_GNB >= a_RF and a_GNB >= a_SVC THEN 'a_GNB'
WHEN a_SVC>= a_LOG and a_SVC >= a_RF and a_SVC >= a_GNB THEN 'a_SVC' END

,a_2nd = I could not figure it out in an easy way !!
,a_3rd = I could not figure it out in an easy way !!
,a_4th = I could not figure it out in an easy way !!

I wonder if someone has a better method to do that

Answer

You can UNPIVOT and then PIVOT your data within a CROSS APPLY

with cte as (
Select * 
 From  YourTable
 Cross Apply (
               Select d1 = max(case when RN=1 then item end)
                     ,d2 = max(case when RN=2 then item end)
                     ,d3 = max(case when RN=3 then item end)
                     ,d4 = max(case when RN=4 then item end)
                 From (
                       Select *
                             ,RN = row_number() over(order by value desc)
                        From ( values ('a_Log',a_Log)
                                     ,('a_RF' ,a_RF)
                                     ,('a_GNB',a_GNB)
                                     ,('a_Svc',a_Svc)
                             ) b(item,value) )B1
                      ) B2
)
Update cte
  set  a_1st = d1
      ,a_2nd = d2
      ,a_3rd = d3
      ,a_4th = d4

Results