How can I pivot this data using a cte or the pivot syntax? Let's say I have these two tables in an MSSQL-Database:


Is there any way to create a query that result in a table like this?


There is no case where there are multiple Leader/Assistant in the TEAMS-Table. Both of these only occur once per project.
There are multiple ways you could do this, including those suggested by the question title, but I find a conditional MAX() the simplest to grok (and most likely to only ever access any table once). You want one row per idproject/nameproject, and then you need to find the team row that has the specific task associated with it. You could just as easily use MIN() instead of MAX(), if you trust your data it will be the same, if you don't trust your data that will just determine how to break ties.

SELECT
  p.idproject,
  p.nameproject,
  leader    = MAX(CASE t.taskTeam WHEN 'leader'    THEN t.nameTeam END),
  assistant = MAX(CASE t.taskTeam WHEN 'assistant' THEN t.nameTeam END)
FROM dbo.PROJECT AS p
INNER JOIN dbo.TEAMS AS t -- plural but project is not?
ON p.idproject = t.idproject
WHERE t.taskTeam IN ('leader', 'assistant')
GROUP BY p.idproject, p.nameproject
ORDER BY p.idproject;


Note: I assumed assistent was a typo, and that taskTeam is varchar.