How to use conditional aggregation on columns which have multiple values in SQL

I need to generate an output to show different role(from table2) as separate columns for an id(from table 1), but the role is not mandatory to be present in the table 2 and sometimes there can be multiple roles also. I am stuck here.

I have tried coalesce, but with that I am not able to get the multiple values into different column

SELECT 
i.id,
i.name,
COALESCE(MAX(CASE WHEN r.ID = i.id THEN r.Role END), 'NA') AS Role,
COALESCE(MAX(CASE WHEN r.Role = rl.Role THEN rl.value END), 'NA') AS value,
COALESCE(MAX(CASE WHEN r.Role = rl.Role   THEN rl.value_2 END), 'NA') AS value_2

FROM employee_id AS i LEFT JOIN employee_role r ON r.id = i.id LEFT JOIN Role rl ON r.role = rl.role GROUP BY i.id, i.name

Please find the table structure below:

Table 1: Employee_id
ID Name
100021 Bob
100023 Tom
100024 Jim

Table 2: Employee_Role
ID Role
100023 user1
100024 Beta_user
100024 user

Table 3: Role
Role value value_2
Beta_user zz 56
user 23 ss
user1 sd 45

Required_Result
ID Name Role value value_2 100021 Bob NA NA NA 100023 Tom user1 sd 45 100024 Jim Beta_user zz 56 100024 Jim user 23 ss

Answer

As far as I can tell, no aggregation is necessary:

SELECT e.id, e.name, er.role,
       COALESCE(rl.value, 'NA') AS value,
       COALESCE(rl.value_2, 'NA') AS value_2
FROM employee_id e LEFT JOIN
     employee_role er
     ON er.id = e.id LEFT JOIN
     Role r
     ON er.role = r.role;