SELECT  U.USER_ID
,       STRING_AGG(CONCAT('[', C.CAFE_CD, '] ', C.CAFE_NM), ', ') WITHIN GROUP (ORDER BY C.CAFE_CD) AS CAFES

FROM USER U
JOIN USER_CAFE C ON C.USER_ID = U.USER_ID
GROUP BY  U.USER_ID;

SQLSERVER 2017에서 부터 되더라.