CODEWITHSUNDEEP
sql-server-2008pivot
Sebastian
Sebastian

Reputation: 53

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.

Upvotes: 5

Views: 1531

Answers (3)

Martin Smith
Martin Smith

Reputation: 453348

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838376

Try a CASE statement:

CASE WHEN [DOUG] > [LATHE] THEN 'DOUG'
     WHEN [DOUG] < [LATHE] THEN 'LATHE'
     ELSE 'No winner'
END AS WinningCandidate

Upvotes: 1

JNK
JNK

Reputation: 65167

If it's only a few fields you can use a CASE statement:

...
CASE WHEN NumVotes1 > NumVotes2 THEN Candidate1
     WHEN NumVotes2 > NumVotes1 THEN Candidate2
     ELSE 'TIE' END as WinningCandidate

Upvotes: 0

Related Questions