Query that puts 2 rows on 1 line efficiently

 

greenshot_2011-03-03_08-08-09

 

SELECT     [Group], PhaseType, PhaseCode, Phase, ProjectUID, SortOrder, MAX(CASE Source WHEN 1 THEN StartTask END) AS StartTask,
                      MAX(CASE Source WHEN 2 THEN Endtask END) AS Endtask
FROM         (SELECT     TOP (100) PERCENT dbo.vRIM_WSS_StdMilestonePhaseDurations.[Group], dbo.vRIM_WSS_StdMilestonePhaseDurations.PhaseType,
                                              dbo.vRIM_WSS_StdMilestonePhaseDurations.PhaseCode, dbo.vRIM_WSS_StdMilestonePhaseDurations.[Phase Name] AS Phase, CONVERT(varchar(50),
                                              vRIM_Tasks_StandardMilestones_1.TaskUID) AS StartTask, NULL AS EndTask, dbo.vRIM_WSS_StdMilestonePhaseDurations.SortOrder, 1 AS Source,
                                              ProjectServer_Reporting.dbo.MSP_EpmProject.ProjectUID, ProjectServer_Reporting.dbo.MSP_EpmProject.ProjectName
                       FROM          ProjectServer_Reporting.dbo.MSP_EpmTask INNER JOIN
                                              ProjectServer_Reporting.dbo.MSP_EpmProject ON
                                              ProjectServer_Reporting.dbo.MSP_EpmTask.ProjectUID = ProjectServer_Reporting.dbo.MSP_EpmProject.ProjectUID INNER JOIN
                                              dbo.vRIM_WSS_StdMilestonePhaseDurations INNER JOIN
                                              dbo.vRIM_Tasks_StandardMilestones AS vRIM_Tasks_StandardMilestones_1 ON
                                              dbo.vRIM_WSS_StdMilestonePhaseDurations.StartMilestone = vRIM_Tasks_StandardMilestones_1.[Name-Standard] ON
                                              ProjectServer_Reporting.dbo.MSP_EpmTask.TaskUID = vRIM_Tasks_StandardMilestones_1.TaskUID
                       UNION
                       SELECT     TOP (100) PERCENT vRIM_WSS_StdMilestonePhaseDurations_1.[Group], vRIM_WSS_StdMilestonePhaseDurations_1.PhaseType,
                                             vRIM_WSS_StdMilestonePhaseDurations_1.PhaseCode, vRIM_WSS_StdMilestonePhaseDurations_1.[Phase Name] AS Phase, NULL AS StartTask,
                                             CONVERT(varchar(50), dbo.vRIM_Tasks_StandardMilestones.TaskUID) AS EndTask, vRIM_WSS_StdMilestonePhaseDurations_1.SortOrder, 2 AS Source,
                                             MSP_EpmProject_1.ProjectUID, MSP_EpmProject_1.ProjectName
                       FROM         ProjectServer_Reporting.dbo.MSP_EpmTask AS MSP_EpmTask_1 INNER JOIN
                                             ProjectServer_Reporting.dbo.MSP_EpmProject AS MSP_EpmProject_1 ON MSP_EpmTask_1.ProjectUID = MSP_EpmProject_1.ProjectUID INNER JOIN
                                             dbo.vRIM_Tasks_StandardMilestones INNER JOIN
                                             dbo.vRIM_WSS_StdMilestonePhaseDurations AS vRIM_WSS_StdMilestonePhaseDurations_1 ON
                                             dbo.vRIM_Tasks_StandardMilestones.[Name-Standard] = vRIM_WSS_StdMilestonePhaseDurations_1.EndMilestone ON
                                             MSP_EpmTask_1.TaskUID = dbo.vRIM_Tasks_StandardMilestones.TaskUID) AS StartendTasks
GROUP BY [Group], PhaseType, PhaseCode, Phase, ProjectUID, SortOrder

Advertisements