SQLServer 2017 이상이라면 이런 식으로 풀 수 있습니다.

STRING_SPLIT()과 STRING_AGG() 용법을 살펴 보세요.

CREATE TABLE dbo.MY_TABLE (
  id int NOT NULL IDENTITY PRIMARY KEY,
  col1 nvarchar(10) NOT NULL,
  code_code varchar(50) NOT NULL
);

CREATE TABLE dbo.CODE_TABLE (
  code_code varchar(10) NOT NULL PRIMARY KEY,
  code_name nvarchar(10) NOT NULL
);

INSERT dbo.MY_TABLE (col1, code_code)
VALUES (N'홍길동', 'AA11,AA12,AA13'), (N'심청', 'AA12,AA13');
  
INSERT dbo.CODE_TABLE (code_code, code_name)
VALUES ('AA11', N'아아1'), ('AA12', N'아아2'), ('AA13', N'아아3');

WITH A AS (
    SELECT MT.id, STRING_AGG(CT.code_name, ',') AS code_name
    FROM MY_TABLE MT
        CROSS APPLY STRING_SPLIT(MT.code_code, ',') SS
        INNER JOIN CODE_TABLE CT ON CT.code_code = SS.value
    GROUP BY MT.id
)
SELECT MT.id, MT.col1, A.code_name
FROM MY_TABLE MT
    INNER JOIN A ON A.id = MT.id;

WITH code_table AS
(
SELECT 'AA11' code, '아아1' code_name
UNION ALL SELECT 'AA12', '아아2'
UNION ALL SELECT 'AA13', '아아3'
)
, my_table AS
(
SELECT 'AA11,AA12,AA13' code_code
UNION ALL SELECT 'AA11,AA13'
UNION ALL SELECT 'AA13,AA12'
)
SELECT code_code
     , STUFF(
       (SELECT ',' + code_name
          FROM code_table
         WHERE CHARINDEX(code, a.code_code) > 0
         ORDER BY CHARINDEX(code, a.code_code)
           FOR XML PATH('')
        ), 1, 1, '') code_name
  FROM my_table a
;
-- http://gurubee.net/article/55512