Google Sheets Text Join to consolidate rows into columns

Question

I have a "database" table in sheets where each row is a person/email associated with a company and contact role. So, a company will have multiple rows of contacts for various roles (sales, marketing, etc) and can have more than one person that does the same role. My goal is to consolidate all people at a company that perform the same role into a column per role.

The goal is to go from each company having multiple rows to each company having a single row and the contacts consolidated into columns. "Partnership" row becomes a column, "Primary Sales" row becomes a column...etc.

The cells with red have calculations. This gets me close but I'm looking to perfect it!

=TEXTJOIN(", ",TRUE,QUERY($A$2:$D,"SELECT C,D WHERE A = '"&$F3&"' AND B = '"&G$2&"'"))

1. Can a single calculation in cell G3 populate down for each company?
2. Can the people be separated with a CR/LF rather than a comma e.g.

This

instead of

Example Data: https://docs.google.com/spreadsheets/d/17Nd6jYW_CkTb6Xju9JMDbRAGJnz-TXvcZOFU-lGQBdA/edit?usp=sharing
In the example, existing calculations are in red.

As always, thank you in advance for any help you can give me!

Answer 1

paste in G3 and drag to the right:

=ARRAYFORMULA(IFNA(VLOOKUP($F3:$F, SUBSTITUTE(REGEXREPLACE(REGEXREPLACE(
 TRIM(SPLIT(TRANSPOSE(QUERY(QUERY({$B3:$B, $A3:$A&"♦", $C3:$C&", "&$D3:$D&"♠"}, 
 "select max(Col3) where Col1 = '"&G2&"' group by Col3 pivot Col2")
 ,,999^99)), "♦")), "♠ ", "♠"), "♠$", ), "♠", CHAR(10)), 2, 0)))

spreadsheet demo