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

Question

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.

Answer 1

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;