How can you find the max of several columns that are created in a pivot table using a SQL Server 2008 pivot table query?
Given:
create table ElectionResults_test
(
Total_Votes int,
Precinct_Name varchar(50),
Candidate_Name varchar(50)
)
insert into ElectionResults_test values (4,'CP01', 'DOUG')
insert into ElectionResults_test values (2,'CP02', 'DOUG')
insert into ElectionResults_test values (2,'CP01', 'LATHE')
insert into ElectionResults_test values (4,'CP02', 'LATHE')
SELECT Precinct_Name as ConsPrecinct_Name, 'DOUG' AS Candidate1, [DOUG] AS NumVotes1,
'LATHE' AS Candidate2, [LATHE] AS NumVotes2, 'Needs Data' as WinningCandidate FROM
(Select Total_Votes, Precinct_Name, Candidate_Name from [ELECTIONRESULTS_test])
as SourceTable pivot (sum(Total_Votes) for Candidate_Name in ([DOUG], [LATHE])) as PivotTable
The select statement above has the following output:
ConsPrecinct_name Candidate1 NumVotes1 Candidate2 NumVotes2 Winning Candidate
CP01 DOUG 4 LATH 2 Needs Data
CP01 DOUG 2 LATH 4 Needs Data
The goal is to have the 'Winning Candidate' field populated with the candidate name that has the most votes in the corresponding NumVotes field.
To deal with 8 way contests more easily you can use CROSS APPLY
and VALUES
, you may also want a GROUP BY
as you haven't said how ties will be handled (this will return multiple rows for each winner)
SELECT Precinct_Name AS ConsPrecinct_Name,
'DOUG' AS Candidate1,
[DOUG] AS NumVotes1,
'LATHE' AS Candidate2,
[LATHE] AS NumVotes2,
WinningCandidate.name AS WinningCandidate
FROM (SELECT Total_Votes,
Precinct_Name,
Candidate_Name
FROM ElectionResults_test) AS SourceTable PIVOT (SUM(Total_Votes) FOR
Candidate_Name IN ([DOUG], [LATHE])) AS PivotTable
CROSS APPLY (SELECT CASE
WHEN COUNT(*) = 1 THEN MAX(name)
ELSE 'Tie'
END AS name
FROM (SELECT TOP 1 WITH TIES name
FROM (VALUES('DOUG', [DOUG]),
('LATHE', [LATHE])) Y(name, votes)
ORDER BY votes DESC) T)AS WinningCandidate
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With