SQL Server 2005 how to use pivot instead of a bunch of case statments?

Please show me how I can modify this SQL se开发者_StackOverflowlect to use pivot instead of all the case testaments.

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

---

Something like this:

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