SQL 数据透视表中多列的最大值

Question

如何找到使用 SQL Server 2008 数据透视表查询在数据透视表中创建的最大列数？

给定：

    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

上面的 select 语句具有以下输出：

ConsPrecinct_name  Candidate1  NumVotes1  Candidate2  NumVotes2  Winning Candidate
CP01               DOUG        4          LATH        2          Needs Data
CP01               DOUG        2          LATH        4          Needs Data

目标是用在相应 NumVotes 字段中得票最多的候选人姓名填充“获胜候选人”字段。

Answer 1

要更轻松地处理 8 路竞赛，您可以使用 CROSS APPLY 和 VALUES，您可能还需要 GROUP BY，正如您没有说过的那样如何处理平局（这将为每个获胜者返回多行）

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  

Answer 2

尝试使用 CASE 语句：

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

Answer 3

如果只有几个字段，您可以使用 CASE 语句：

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