Another pivot SQL Server query

I cannot quite get my had around previous examples, unfortunately.

I am trying to create a table based on the voting for baseballs MVP over the years and comparing it with a more objective measure WAR now available. I want to see the difference between the two top ranked players according to this value. Here is code to show just a couple of years of many

select season,division,player,rankMVP as 开发者_C百科MVP,WAR,rankWAR 
 from mlbmvpvoting
where   (season = '1955' or season = '1956')
and rankWAR < 3
order by season,division,rankWAR

season division   player                   MVP  WAR  rankWAR
1955    AL  Mickey Mantle                   5   9.5     1
1955    AL  Al Kaline                       2   9.0     2
1955    NL  Willie Mays                     4   9.3     1
1955    NL  Duke Snider                     2   8.9     2
1956    AL  Mickey Mantle                   1   12.9    1
1956    AL  Early Wynn                      13  8.5     2
1956    NL  Duke Snider                     10  7.7     1
1956    NL  Hank Aaron                      3   7.5     2

I want to produce something like this

season   division  pl_1         pl_2     MVP_1   MVP_2   WAR_1   WAR_2  
 1955      AL  Mickey Mantle  Al Kaline   5      2       9.5      9.0
 1955      N   Willy Mays     Duke Snider 4      2       9.3      8.9

etc

TIA

---

Use grouping and conditional aggregating, like this:

SELECT
  season,
  division,
  pl_1  = MAX(CASE rankWAR WHEN 1 THEN player  END),
  pl_2  = MAX(CASE rankWAR WHEN 2 THEN player  END),
  MVP_1 = MAX(CASE rankWAR WHEN 1 THEN rankMVP END),
  MVP_2 = MAX(CASE rankWAR WHEN 2 THEN rankMVP END),
  WAR_1 = MAX(CASE rankWAR WHEN 1 THEN WAR     END),
  WAR_2 = MAX(CASE rankWAR WHEN 2 THEN WAR     END)
FROM mlbmvpvoting
WHERE season IN ('1955', '1956')
  AND rankWAR IN (1, 2)
GROUP BY season, division
ORDER BY season, division

IN is basically more optimisable than OR and <, so I changed the conditions accordingly.

---

See if such a solution suits you:

with mlbmvpvoting (season, division, player, MVP, WAR, rankWAR) as 
(
    select 1955,    'AL', 'Mickey Mantle',  5,   9.5,     1  union all
    select 1955,    'AL', 'Al Kaline',      2,   9.0,     2  union all
    select 1955,    'NL', 'Willie Mays',    4,   9.3,     1  union all
    select 1955,    'NL', 'Duke Snider',    2,   8.9,     2  union all
    select 1956,    'AL', 'Mickey Mantle',  1,   12.9,    1  union all
    select 1956,    'AL', 'Early Wynn',     13,  8.5,     2  union all
    select 1956,    'NL', 'Duke Snider',    10,  7.7,     1  union all
    select 1956,    'NL', 'Hank Aaron',     3,   7.5,     2
)
select
    season, 
    division,
    pl_1  = max(case rankWAR when 1 then player end),
    pl_2  = max(case rankWAR when 2 then player end),
    MVP_1 = max(case rankWAR when 1 then MVP end),
    MVP_2 = max(case rankWAR when 2 then MVP end),
    WAR_1 = max(case rankWAR when 1 then WAR end),
    WAR_2 = max(case rankWAR when 2 then WAR end)
from mlbmvpvoting
group by season, division
order by season;