SQL Server 2005 如何透视或交叉表数据来获取结果集？

Question

我希望生成一个存储过程，它将采用 projectIds 的 CSV 字符串并返回以下结果集。

示例数据

projectId 和 name 字段来自项目表，而其余字段是每个项目的里程碑日期的关键。里程碑字段适用于 (37, 39, 41, 45, 47, 50, 53, 72, 73, 75, 77) 中的某些 CID 里程碑，我希望每个相应的里程碑在 excel 文件中显示为名称（I我猜测为每个里程碑使用别名将每个里程碑名称转换为我希望它显示的内容）

另请注意，第一个里程碑字段是 BRS (cid=37) 开始日期，其余里程碑字段都是结束日期，包括里程碑 cid 37 和上面提到的其余部分。

日期应代表具有可用数据的项目里程碑日期，如果特定 CID 没有项目里程碑，那么我需要使用 ReleaseSchedule 日期。我打算通过 COALESCE（项目里程碑日期、发布计划日期）来完成此任务。

里程碑名称的字段标题如下：

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

Answer 1

@beth，我现在可以使用下面的代码为单个项目tId 进行此工作（某种程度上）。但我没有使用透视，而是手动使用 case 语句来在最后旋转数据：

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  

Answer 2

不确定您是否可以完全自动化您想要的一切。

回复：我希望每个里程碑在 Excel 文件中显示为名称（我猜测为每个里程碑使用别名将每个里程碑名称转换为我希望它显示的名称）

你可以使用查找表？

回复：另请注意，第一个里程碑字段是 BRS (cid=37) 开始日期，其余里程碑字段都是结束日期

在数据透视之前，这些字段必须位于等效输出中柱子。在数据透视期间，您必须对值应用聚合函数，例如first()、min() 或max()。

回复：日期应代表具有可用数据的项目里程碑日期，如果特定 CID 没有项目里程碑，那么我需要使用 ReleaseSchedule 日期。

再次，您需要执行此工作在枢轴之前。

您能否以您想要的方式获得未透视的结果集，将项目与projectMilestone 和releaseSchedule 结合（仅适用于projectMilestone 中的间隙）并查找您想要显示的列标题名称？