Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL max of multiple columns in pivot table

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.

like image 634
Sebastian Avatar asked Nov 04 '22 12:11

Sebastian


1 Answers

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  
like image 164
Martin Smith Avatar answered Nov 09 '22 15:11

Martin Smith