How to Pivot table and provide Ranking with TSQL

Question

How to pivot table from below format:

to the following desired format:

It would be really great help, if some one can help me to resolve this issue

Note: I am using SQL Server 2008 R2

Answer 1

Try the following statements:

DECLARE @DataSource TABLE
(
    [RegionalManager] CHAR(1)
   ,[ATVScore] SMALLINT
   ,[BTVScore] SMALLINT
   ,[CTVScore] SMALLINT
   ,[DTVScore] SMALLINT
);

INSERT INTO @DataSource ([RegionalManager], [ATVScore], [BTVScore], [CTVScore], [DTVScore])
VALUES ('A', 75, 56, 34, 567)
      ,('B', 36, 678, 5, 36)
      ,('C', 61, 67, 56, 61)
      --,('A', 71, 71, 71, 71)
      --,('B', 36, 36, 36, 36)
      --,('C', 61, 61, 61, 61)
      --,('A', 71, 71, 71, 71)
      --,('B', 36, 36, 36, 36)
      --,('C', 61, 61, 61, 61);

SELECT UNPVT.[RegionalManager]
      ,UNPVT.[MeasureName]
      ,UNPVT.[Score]
      ,ROW_NUMBER() OVER (PARTITION BY UNPVT.[MeasureName] ORDER BY UNPVT.[Score] DESC) AS [Rank]
FROM @DataSource
UNPIVOT
(
    [Score] FOR [MeasureName] IN ([ATVScore], [BTVScore], [CTVScore], [DTVScore])
) UNPVT
ORDER BY UNPVT.[MeasureName] 
        ,UNPVT.[RegionalManager];

Note, that if you uncomment the insertion of the values into the @DataSource table, you will get something like this:

This is caused by having more regional mangers scores values for the ATVScore measure type. You can try these ranking functions to find the most appropriate for your needs ranking.