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;
精彩评论