I have a table like below -

This solution should at least give you an idea to complete your case with all 15 roles. You can either just repeat/add extra CTEs(temp_roleN) and same amount of JOINS without any extra changes OR you can try to generalize above code and make it more generic - for example using some scripting with EXECUTE IMMEDIATE, etc.

CodePudding user response：

Consider also below approach - it is very easily extended to any numbers of roles - just add them in respective lists: (Role1, Role2, Role3) and ('Role1', 'Role2', 'Role3')

select * except(pos)
from (
  select *, row_number() over(partition by Project_Name, role order by user) pos
  from your_table
  unpivot (user for role in (Role1, Role2, Role3))
)
pivot (any_value(user) for role in ('Role1', 'Role2', 'Role3'))    

if applied to sample data in your question - output is