I am not sure what database you are using, but below is the query which resolves your problem and that works well in Oracle 11g.
SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
,(CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL)
- (CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
) MSGNUM_TO_ID
FROM
-- REPLACE FOLLOWING BLOCK WITH YOUR FIRST TABLE
(SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
) T1
-- REPLACE FOLLOWING BLOCK WITH YOUR SECOND TABLE
,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
;
Thanks & Regards, Niral Soni
