SELECT  U.SYS_ID
,       U.USR_ID
,       STRING_AGG(CONCAT('[', H.ROLE_CD, '] ', H.ROLE_NM), ', ') WITHIN GROUP (ORDER BY H.ROLE_CD) AS ROLES

FROM ESAUSER U
JOIN ESAAURP P  ON  P.SYS_ID    = U.SYS_ID
                AND P.USR_ID    = U.USR_ID

JOIN ESAAURH H  ON  H.SYS_ID    = P.SYS_ID
                AND H.ROLE_CD   = P.ROLE_CD
                AND H.STS      != 'D'

WHERE U.SYS_ID  = '100'
AND U.USR_ID    = 'TEST_ID'

GROUP BY    U.SYS_ID
,           U.USR_ID;

SQLSERVER 2017에서 부터 되더라.