主页 / dba / 问题 / 875
Accepted
kacalapy
Asked: 2011-01-26 08:44:50 +0800 CST

SQL Server 2005：如何使用数据透视而不是一堆案例语句？

  • 772

请告诉我如何修改这个 SQL 选择以使用 pivot 而不是所有的 case 语句。

SELECT t.ProjectId,    
   COALESCE(MAX(ExecutiveChampion), 'n/a'),    
   COALESCE(MAX(BusinessOwner), 'n/a'),    
   COALESCE(MAX(BusinessAnalyst), 'n/a'),    
   COALESCE(MAX(GeneralContractor), 'n/a'),    
   COALESCE(MAX(PrimaryPM),  'n/a'),    
   COALESCE(MAX(DevelopmentManager), 'n/a'),    
   COALESCE(MAX(DevelopmentLead), 'n/a'),    
   COALESCE(MAX(TDM), 'n/a'),    
   COALESCE(MAX(PTM), 'n/a')    
FROM (    
    SELECT pl.ProjectId,     
           CASE WHEN StakeholderCID = 95  THEN FullName ELSE NULL END as 'ExecutiveChampion',    
           CASE WHEN StakeholderCID = 96  THEN FullName ELSE NULL END as 'BusinessOwner',    
           CASE WHEN StakeholderCID = 97  THEN FullName ELSE NULL END as 'BusinessAnalyst',    
           CASE WHEN StakeholderCID = 100 THEN FullName ELSE NULL END as 'GeneralContractor',    
           CASE WHEN StakeholderCID = 101 THEN FullName ELSE NULL END as 'PrimaryPM',    
           CASE WHEN StakeholderCID = 102 THEN FullName ELSE NULL END as 'DevelopmentManager',    
           CASE WHEN StakeholderCID = 103 THEN FullName ELSE NULL END as 'DevelopmentLead',    
           CASE WHEN StakeholderCID = 104 THEN FullName ELSE NULL END as 'TDM',    
           CASE WHEN StakeholderCID = 105 THEN FullName ELSE NULL END as 'PTM'    
      FROM @pList pl    
     INNER JOIN StatusCode sc    
        ON 1 = 1    
       AND SCID IN (8, 9)    
      LEFT OUTER JOIN ProjectStakeholder ps    
        ON pl.ProjectId = ps.ProjectId    
       AND sc.CID = ps.StakeholderCID     
   ) as t    
GROUP BY t.ProjectId
sql-server-2005 syntax

2 个回答

  1. 调整留给请求者作为练习。

    使用 PIVOT 和 UNPIVOT

    SELECT
    ProjectId,
    [95] AS [ExecutiveChampion],
    [96] AS [...],
    [97] AS [...],
    [100] AS [...],
    [101] AS [...],
    [102] AS [...],
    [103] AS [...],
    [104] AS [...],
    [105] AS [...]
FROM (
    SELECT
        pl.ProjectId,
        StakeholderCID,
        FullName
    FROM @pList pl
    INNER JOIN StatusCode sc
        ON 1 = 1
            AND SCID IN (8, 9)
    LEFT OUTER JOIN ProjectStakeholder ps
        ON pl.ProjectId = ps.ProjectId
            AND sc.CID = ps.StakeholderCID
) AS SourceTable
PIVOT (
    MAX(FullName)
    FOR StakeholderCID IN ([95], [96], [97], [100], [101], [102], [103], [104], [105])
) AS PivotTable;
    • 8
  2. Best Answer

    完整答案如下：

    SELECT  ProjectId,
        ISNULL([95],'n/a') ExecutiveChampion,
        ISNULL([96],'n/a') BusinessOwner,
        ISNULL([97],'n/a') BusinessAnalyst,
        ISNULL([100],'n/a') GeneralContractor,
        ISNULL([101],'n/a') PrimaryPM,
        ISNULL([102],'n/a') DevelopmentManager,
        ISNULL([103],'n/a') DevelopmentLead,
        ISNULL([104],'n/a') TDM,
        ISNULL([105],'n/a') PTM
FROM (  SELECT pl.ProjectId, StakeholderCID, FullName
        FROM @pList pl
        INNER JOIN StatusCode sc
            ON 1 = 1
            AND SCID IN (8, 9)
        LEFT JOIN ProjectStakeholder ps
            ON pl.ProjectId = ps.ProjectId
            AND sc.CID = ps.StakeholderCID) AS ST
PIVOT (MAX(FullName) FOR StakeholderCID IN ([95], [96], [97], [100], [101], [102], [103], [104], [105])) AS PT
    • 2

相关问题