Average and sort by this based on other conditional columns in a table-CodePudding

I have a table in SQL Server 2017 like below:

Name  Rank1  Rank2  Rank3  Rank4
Jack  null    1     1      3
Mark  null    3     2      2
John  null    2     3      1

What I need to do is to add an average rank column then rank those names based on those scores. We ignore null ranks. Expected output:

Name  Rank1  Rank2  Rank3  Rank4  AvgRank FinalRank
Jack  null    1     1      3       1.66    1
Mark  null    3     2      2       2.33    3
John  null    2     3      1       2       2

My query now looks like this:

;with cte as (
   select *, AvgRank= (Rank1 Rank2 Rank3 Rank4)/@NumOfRankedBy
   from mytable
)
select *, FinakRank= row_number() over (order by AvgRank)
from cte

I am stuck at finding the value of @NumOfRankedBy, which should be 3 in our case because Rank1 is null for all.

What is the best way to approach such an issue?

Thanks.

CodePudding user response：

Your conumdrum stems from the fact your table in not normalised and you are treating data (Rank) as structure (columns).

You should have a table for Ranks where each rank is a row, then your query is easy.

You can unpivot your columns into rows and then make use of avg

select *, FinakRank = row_number() over (order by AvgRank)
from mytable
cross apply (
  select Avg(r * 1.0) AvgRank
  from (values(rank1),(rank2),(rank3),(rank4))r(r)
)r;