How to group this table properly?

Here’s my dataset with expected out. But I am having issues doing it.

Also, I attached the query I created and the output is not correct. could you please point me to where my mistake is?

DROP TABLE #TEMP_ROLES
DROP TABLE #ORG_ROLES
CREATE TABLE #TEMP_ROLES
(
    ID INT,
    DESCRIPTIONS CHAR(15)
)

INSERT INTO #TEMP_ROLES VALUES (1, 'ROLE 1')
INSERT INTO #TEMP_ROLES VALUES (2, 'ROLE 2')
INSERT INTO #TEMP_ROLES VALUES (3, 'ROLE 3')
INSERT INTO #TEMP_ROLES VALUES (4, 'ROLE 4')
INSERT INTO #TEMP_ROLES VALUES (5, 'ROLE 5')

CREATE TABLE #ORG_ROLES
(
    ID INT,
    NAME CHAR(15),
)

INSERT INTO #ORG_ROLES VALUES (1, 'NAME_1')
INSERT INTO #ORG_ROLES VALUES (2, 'NAME_1')
INSERT INTO #ORG_ROLES VALUES (3, 'NAME_1')
INSERT INTO #ORG_ROLES VALUES (1, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (2, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (3, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (4, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (5, 'NAME_2')
INSERT INTO #ORG_ROLES VALUES (1, 'NAME_3')

This is the query I created but the output is incorrect.

SELECT NAME,
CASE WHEN TR.ID = 1 THEN 'YES' ELSE 'NO'  END  AS ROLE_1,
CASE WHEN TR.ID = 2 THEN 'YES' ELSE 'NO'  END  AS ROLE_2,
CASE WHEN TR.ID = 3 THEN 'YES' ELSE 'NO' END  AS ROLE_3,
CASE WHEN TR.ID = 4 THEN 'YES' ELSE 'NO'  END  AS ROLE_4,
CASE WHEN TR.ID = 5 THEN 'YES' ELSE 'NO' END  AS ROLE_5
FROM #TEMP_ROLES TR
LEFT JOIN #ORG_ROLES R ON TR.ID = R.ID

Expected output:

NAME           ROLE_1   ROLE_2  ROLE_3  ROLE_4  ROLE_5
NAME_1          YES      YES      YES     NO      NO
NAME_2          YES      YES      NO      YES     YES
NAME_3          YES      NO       NO      NO      NO

Answer

You’re almost there – you just need to add aggregation:

SELECT  NAME,
        MAX(CASE WHEN TR.ID = 1 THEN 'YES' ELSE 'NO'  END) AS ROLE_1,
        MAX(CASE WHEN TR.ID = 2 THEN 'YES' ELSE 'NO'  END)  AS ROLE_2,
        MAX(CASE WHEN TR.ID = 3 THEN 'YES' ELSE 'NO' END)  AS ROLE_3,
        MAX(CASE WHEN TR.ID = 4 THEN 'YES' ELSE 'NO'  END)  AS ROLE_4,
        MAX(CASE WHEN TR.ID = 5 THEN 'YES' ELSE 'NO' END)  AS ROLE_5
FROM #TEMP_ROLES TR
LEFT JOIN #ORG_ROLES R ON TR.ID = R.ID
GROUP BY NAME