Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question

2 Answers 2

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;
share|improve this answer
up vote 2 down vote accepted

full answer below:

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.