Project Server 2010 - SQL- Get Project Tasks & Resources

This SQL query returns the Project Tasks data by all resources assigned to Task for Projects from Project server reporting database. This query might be useful for PMO/ Project Managers to have a quick look on the project/Tasks & Resources assigned to Tasks. This can be used in SSRS reports/Excel reports or in .NET webparts. Project Name can be passed dynamically to fetch the milestone count

Tasks and Resources for projects.sql :

SELECT     
            dbo.MSP_EpmAssignment_UserView.ProjectUID,
            dbo.MSP_EpmAssignment_UserView.TaskUID,
            dbo.MSP_EpmProject_UserView.ProjectName,
            dbo.MSP_EpmTask_UserView.TaskName,
            dbo.MSP_EpmAssignment_UserView.ResourceUID,
            dbo.MSP_EpmResource_UserView.ResourceName,
            dbo.MSP_EpmResource_UserView.ResourceInitials
           
INTO #TempTable

FROM         dbo.MSP_EpmAssignment_UserView INNER JOIN
dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmAssignment_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID INNER JOIN
dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmAssignment_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID INNER JOIN
dbo.MSP_EpmResource_UserView ON dbo.MSP_EpmAssignment_UserView.ResourceUID = dbo.MSP_EpmResource_UserView.ResourceUID

SELECT
  ProjectUID,
  TaskUID,
  ProjectName,
  TaskName,
  STUFF((
    SELECT ', ' + ResourceInitials 
    FROM #TempTable
    WHERE (TaskUID = Results.TaskUID)
    FOR XML PATH (''))
  ,1,2,'') AS ResourceInitialsCombined,
   STUFF((
    SELECT ', ' + ResourceName 
    FROM #TempTable
    WHERE (TaskUID = Results.TaskUID)
    FOR XML PATH (''))
  ,1,2,'') AS ResourceNameCombined
FROM #TempTable Results
GROUP BY TaskUID,ProjectUID,ProjectName,TaskName

DROP TABLE #TempTable