How to give ranks to multiple columns data in SQL Server?

  • A+
Category:Languages

I have input table as shown below -

ID  Name q1     q2      q3      q4 1   a    2621   2036    1890    2300 2   b    18000  13000   14000   15000 3   c    100    200     300     400 

I want ranking of columns(q1, q2, q3 and q4) data for each row. For example, if I consider last row of above input, then q4 column contains 400 value which is higher than other columns, so rank to q4 column will be 1, q3 rank will be 2, q2 rank will be 3 and q1 rank will be 4.

I am looking for output like -

id  name  q1  q2  q3  q4 1   a     1   3   4   2 2   b     1   4   3   2 3   c     4   3   2   1 

There are more than 100,000 records present in input table.

I have created small SQL script for input table i.e.,

declare @temp table (ID int, Name varchar(10), q1 int, q2 int, q3 int, q4 int)  insert into @temp select 1, 'a', 2621, 2036, 1890, 2300 union all select 2, 'b', 18000, 13000, 14000, 15000 union all select 3, 'c', 100, 200, 300, 400  select * from @temp 

Please help me to find efficient way to solve this problem.

 


You need UNPIVOT & PIVOT :

SELECT Id, Name,         MAX(CASE WHEN qname = 'q1' THEN SEQ END),        MAX(CASE WHEN qname = 'q2' THEN SEQ END),        MAX(CASE WHEN qname = 'q3 'THEN SEQ END),        MAX(CASE WHEN qname = 'q4 'THEN SEQ END) FROM (SELECT t.*, tt.*,               DENSE_RANK() OVER (PARTITION BY t.Name ORDER BY tt.qq DESC) AS SEQ       FROM @temp t CROSS APPLY            ( VALUES (q1, 'q1'), (q2, 'q2'), (q3, 'q3'), (q4, 'q4')             ) tt(qq, qname)      ) t GROUP BY t.Id, t.Name; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: