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
Advertisement
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