How can I pivot this data using a cte or the pivot syntax?

Question

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.

Answer 1

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.

Answer 2

I'd do this with cte's (common table expressions). By breaking up the Teams table based on the taskteam column, then joining them together on the idproject column:

    with leaders as (select idproject, nameTeam
                     from teams
                    where taksteam = 'leader'),
    assistants as (select idproject, nameTeam
                     from teams
                    where taksteam = 'assistant'),
    poor_guys as (select idproject, nameTeam
                     from teams
                    where taksteam = 'poor guy')


    select p.*, l.name, a.name, pg.name
    from project p
    left join leaders l on p.idproject = l.idproject
    left join assistants a on p.idproject = l.idproject
    left join poor_guys pg on p.idproject = pg.idproject
    order by p.name_project

If you've got more than three people in a team, you are going to run into additional complications though - you'll want to use the rank() function to further seperate out your teams' roles.