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

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

CodePudding user response：

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

CodePudding user response：

You can use Cross Apply with Values to unpivot the columns and use Row number to apply ordering, then use an updatable CTE to update the original table:

with u as (
    select * 
    from targets
    cross apply (
        select 
            Max(case when rn=1 then c end) [1],
            max(case when rn=2 then c end) [2],
            max(case when rn=3 then c end) [3],
            max(case when rn=4 then c end) [4]
        from (
            select c, Row_Number() over(order by v desc) rn
            from (values(a_LOG, 'a_LOG'), (a_RF, 'a_RF'), (a_GNB, 'a_GNB'), (a_SVC, 'a_SVC') )v(v,c)
        )c
    )p
)
update u set
    u.a_1st=[1],
    u.a_2nd=[2],
    u.a_3rd=[3],
    u.a_4th=[4]

Demo Fiddle