WITH t1 AS ( SELECT 1 id, '철수' nm UNION ALL SELECT 2, '영희' UNION ALL SELECT 3, '수영' ) , t2 AS ( SELECT 'a' id, '1|' id_list UNION ALL SELECT 'b', '1|3|' UNION ALL SELECT 'c', '1|2|3|' ) SELECT id , (SELECT nm + '|' FROM t1 b WHERE CHARINDEX('|'+CAST(b.id AS VARCHAR)+'|', '|'+a.id_list) > 0 ORDER BY b.id FOR XML PATH('') ) nm_list FROM t2 a ORDER BY id ;