Another pivot SQL Server query

Question

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 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

Andriy M · Accepted Answer · 2011-06-23 18:08:08Z

1

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.

answered Jun 23, 2011 at 18:08

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pssguy Over a year ago

Thanks for that . I just extended to all seasons with WHERE season IN (select distinct season FROM mlbmvpvoting ) and was away. If anyone is interested in an analysis using the results - particularly pertaining to Willie Mays - I have blogged about it here tinyurl.com/6kr28dl

Vadim Loboda · Accepted Answer · 2011-06-23 18:07:08Z

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;

Collectives™ on Stack Overflow

Another pivot SQL Server query

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related