SQL Server 2005 how do I pivot or cross tab data to get my resultset?_问答_开发者

I am looking to generate a stored proc that will take a CSV string of projectIds and return the following result set.

SQL Server 2005 how do I pivot or cross tab data to get my resultset?

sample data

the projectId and name fields come from the project table while the rest of the fields are a pivot of the milestone dates for each project. the milestone fields are for certain milestones of CID in (37, 39, 41, 45, 47, 50, 53, 72, 73, 75, 77) where I want each respective milestone to show as the name in the excel file (I am guessing to use an alias for each to convert each milestone name to what I want it to show as)

Also note the first milestone field is the BRS (cid=37) start date and the rest of the milestone fields are all end dates including milestone cid 37 and the rest mentioned开发者_如何转开发 above.

The dates should represent the projectMilestone dates where available data is had, if there is no projectMilestone for a particular CID then i need to use the ReleaseSchedule date. i was going to COALESCE(projectmilestone dates, releaseschedual dates) to accomplish this.

the field headers of the milestone names would be such:

CID           NAME in result set (as field headers)
37            BRS
39            SRS
41            SAD
45            Product Profile Review
47            SE Integration
50            IDE
53            UAT
72            PE Testing
73            Code Freeze
75            Dark Pod
77            Production

@beth i have this working (sort of) now for individual projecttId's with the below code. but instead of using pivot i have manual case statements to pivot the data at the end:

ALTER PROCEDURE [dbo].[rpt_ReportingMilestones]  
( @ProjectId int = null)  
AS  
BEGIN  
    SELECT sco.CID,  
           sco.CodeName,  
           t1.EndDate as rsEndDate,  
           t2.EndDate as pmEndDate,  
     t2.CodeName as RAGStatus  
      INTO #TTT  
      FROM StatusCode sco  
      LEFT OUTER JOIN  
           (SELECT sc.CID,  
                   rs.EndDate  
              FROM StatusCode sc  
             INNER JOIN ReleaseSchedule rs  
                ON sc.CID = rs.MilestoneCID  
             INNER JOIN Project p  
                ON rs.ReleaseID = p.ReleaseID  
               AND p.ProjectId = @ProjectId) as t1  
        ON sco.CID = t1.CID  
      LEFT OUTER JOIN  
           (SELECT sc.CID,  
                   pm.EndDate,  
       sc2.CodeName  
              FROM StatusCode sc  
             INNER JOIN ProjectMilestone pm  
                ON sc.CID = pm.MilestoneCID  
               AND pm.ProjectID = @ProjectId  
    INNER JOIN StatusCode sc2  
       ON pm.RAGStatusCID = sc2.CID) as t2  
        ON sco.CID = t2.CID  
     WHERE sco.CID in (37, 39, 41, 45, 47, 50, 53, 72, 73, 75, 77)  

 CREATE TABLE #UUU  
           (rowid integer not null,  
            rowHeader  nvarchar(50),  
            milestone1 DateTime,  
   ragstatus1 nvarchar(50),  
            milestone2 DateTime,  
   ragstatus2 nvarchar(50),  
            milestone3 DateTime,  
   ragstatus3 nvarchar(50),  
            milestone4 DateTime,  
   ragstatus4 nvarchar(50),  
            milestone5 DateTime,  
   ragstatus5 nvarchar(50),  
            milestone6 DateTime,  
   ragstatus6 nvarchar(50),  
            milestone7 DateTime,  
   ragstatus7 nvarchar(50),  
            milestone8 DateTime,  
   ragstatus8 nvarchar(50),  
            milestone9 DateTime,  
   ragstatus9 nvarchar(50),  
            milestone10 DateTime,  
   ragstatus10 nvarchar(50),  
            milestone11 DateTime,  
   ragstatus11 nvarchar(50))  


    INSERT INTO #UUU  
 SELECT 2 as RowId,   
           'Baseline' as rowHeader,  
           CASE WHEN CID = 37 THEN rsEndDate ELSE null END as Milestone1,  
           null,  
           CASE WHEN CID = 39 THEN rsEndDate ELSE null END as Milestone2,  
           null,  
           CASE WHEN CID = 41 THEN rsEndDate ELSE null END as Milestone3,  
           null,  
           CASE WHEN CID = 45 THEN rsEndDate ELSE null END as Milestone4,  
           null,  
           CASE WHEN CID = 47 THEN rsEndDate ELSE null END as Milestone5,  
           null,  
           CASE WHEN CID = 50 THEN rsEndDate ELSE null END as Milestone6,  
           null,  
           CASE WHEN CID = 53 THEN rsEndDate ELSE null END as Milestone7,  
           null,  
           CASE WHEN CID = 72 THEN rsEndDate ELSE null END as Milestone8,  
           null,  
           CASE WHEN CID = 73 THEN rsEndDate ELSE null END as Milestone9,  
           null,  
           CASE WHEN CID = 75 THEN rsEndDate ELSE null END as Milestone10,  
           null,  
           CASE WHEN CID = 77 THEN rsEndDate ELSE null END as Milestone11,  
           null  
      FROM #TTT  


    INSERT INTO #UUU  
 SELECT 3 as RowId,   
           'Adjusted',  
           CASE WHEN CID = 37 THEN pmEndDate ELSE null END as Milestone1,  
           CASE WHEN CID = 37 THEN RAGStatus ELSE null END as RAGStatus1,  
           CASE WHEN CID = 39 THEN pmEndDate ELSE null END as Milestone2,  
           CASE WHEN CID = 39 THEN RAGStatus ELSE null END as RAGStatus2,  
           CASE WHEN CID = 41 THEN pmEndDate ELSE null END as Milestone3,  
           CASE WHEN CID = 41 THEN RAGStatus ELSE null END as RAGStatus3,  
           CASE WHEN CID = 45 THEN pmEndDate ELSE null END as Milestone4,  
           CASE WHEN CID = 45 THEN RAGStatus ELSE null END as RAGStatus4,  
           CASE WHEN CID = 47 THEN pmEndDate ELSE null END as Milestone5,  
           CASE WHEN CID = 47 THEN RAGStatus ELSE null END as RAGStatus5,  
           CASE WHEN CID = 50 THEN pmEndDate ELSE null END as Milestone6,  
           CASE WHEN CID = 50 THEN RAGStatus ELSE null END as RAGStatus6,  
           CASE WHEN CID = 53 THEN pmEndDate ELSE null END as Milestone7,  
           CASE WHEN CID = 53 THEN RAGStatus ELSE null END as RAGStatus7,  
           CASE WHEN CID = 72 THEN pmEndDate ELSE null END as Milestone8,  
           CASE WHEN CID = 72 THEN RAGStatus ELSE null END as RAGStatus8,  
           CASE WHEN CID = 73 THEN pmEndDate ELSE null END as Milestone9,  
           CASE WHEN CID = 73 THEN RAGStatus ELSE null END as RAGStatus9,  
           CASE WHEN CID = 75 THEN pmEndDate ELSE null END as Milestone10,  
           CASE WHEN CID = 75 THEN RAGStatus ELSE null END as RAGStatus10,  
           CASE WHEN CID = 77 THEN pmEndDate ELSE null END as Milestone11,  
           CASE WHEN CID = 77 THEN RAGStatus ELSE null END as RAGStatus11  
      FROM #TTT  

    SELECT Rowid,   
           rowHeader,  
           MAX(Milestone1) AS 'BRS',   
     MAX(RagStatus1) AS 'BRS RAG',  
           MAX(Milestone2) AS 'SRS',   
     MAX(RagStatus2) AS 'SRS RAG',  
           MAX(Milestone3) AS 'SAD',  
     MAX(RagStatus3) AS 'SAD RAG',  
           MAX(Milestone4) AS 'Product Profile Review',   
     MAX(RagStatus4) AS 'Product Profile Review RAG',  
           MAX(Milestone5) AS 'SE Integration',   
     MAX(RagStatus5) AS 'SE Integration RAG',   
           MAX(Milestone6) AS 'IDE',   
     MAX(RagStatus6) AS 'IDE RAG',  
           MAX(Milestone7) AS 'UAT',   
     MAX(RagStatus7) AS 'UAT RAG',  
           MAX(Milestone8) AS 'PE Testing',   
     MAX(RagStatus8) AS 'PE Testing RAG',  
           MAX(Milestone9) AS 'Code Freeze',   
     MAX(RagStatus9) AS 'Code Freeze RAG',  
           MAX(Milestone10) AS 'Dark Pod',   
     MAX(RagStatus10) AS 'Dark Pod RAG',  
           MAX(Milestone11) AS 'Production',  
     MAX(RagStatus11) AS 'Production RAG'  
      FROM #UUU  
     GROUP BY Rowid, rowHeader  
     ORDER BY RowId  
END

Not sure if you can fully automate everything you want.

re: I want each respective milestone to show as the name in the excel file (I am guessing to use an alias for each to convert each milestone name to what I want it to show as)

can you use a lookup table?

re: Also note the first milestone field is the BRS (cid=37) start date and the rest of the milestone fields are all end dates

Before the pivot, those fields will have to be in an equivalent output column. During the pivot you have to apply an aggregate function to the values, like first(), min(), or max().

re: The dates should represent the projectMilestone dates where available data is had, if there is no projectMilestone for a particular CID then i need to use the ReleaseSchedule date.

again, you'll need to do this work before the pivot.

Can you get the unpivoted result set the way you want joining project with projectMilestone and releaseSchedule (for the gaps in projectMilestone only) and looking up your column header names you want displayed?