SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements.

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

Tweaking is left as an exercise to the requester.

Using PIVOT and 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;

StackExchange Database Administrators Q#875, answer score: 8