ProjectId |
-----------+
1          |
2          |
3          |

ResourceId |    Label      | ProjectId
-----------+---------------+-----------
2          |   Resource X  |  1
3          |   Resource Y  |  1
4          |   Resource Z  |  2
5          |   Resource A  |  2
6          |   Resource X  |  3

ProjectId  |   Label                  |
-----------+--------------------------+
1          |   Resource X, Resource Y |
2          |   Resource Z, Resource A |
3          |   Resource X             |

CREATE TABLE #tmpProjects
(
    ProjectId INT
    , Label VARCHAR(1000)
)

SELECT [P].ProjectId, [PR].Label
    INTO #tmpProjects
FROM [MySchema].[ProjectResources] [PR]
INNER JOIN dbo.Project [P] ON [P].ProjectId= [PR].ProjectId