Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE myTable (
    m_id NUMBER PRIMARY KEY
);
CREATE TABLE myTable_numbers (
    m_id REFERENCES mytable(m_id),
    m_no NUMBER(10,0)
);
INSERT ALL
  INTO myTable (m_id) VALUES (1)
  INTO myTable (m_id) VALUES (2)
  INTO myTable (m_id) VALUES (3)
  INTO myTable_numbers (m_id, m_no) VALUES (1, 1)
  INTO myTable_numbers (m_id, m_no) VALUES (1, 2)
  INTO myTable_numbers (m_id, m_no) VALUES (1, 3)
  INTO myTable_numbers (m_id, m_no) VALUES (3, 2)
  INTO myTable_numbers (m_id, m_no) VALUES (3, 4)
SELECT 1 FROM DUAL;
8 rows affected

SELECT mt.m_id,
       mtn.m_no
FROM   mytable mt
       LEFT OUTER JOIN mytable_numbers mtn
       ON (mt.m_id = mtn.m_id);


M_ID
M_NO


1
1

1
2

1
3

3
2

3
4

2
null


SELECT mt.m_id,
       LISTAGG(mtn.m_no, ',') WITHIN GROUP (ORDER BY mtn.m_no) AS m_nos
FROM   mytable mt
       LEFT OUTER JOIN mytable_numbers mtn
       ON (mt.m_id = mtn.m_id)
GROUP BY mt.m_id;


M_ID
M_NOS


1
1,2,3

2
null

3
2,4


DROP TABLE mytable_numbers;
DROP TABLE mytable;
CREATE TYPE int_list IS TABLE OF NUMBER(10,0);
CREATE TABLE myTable (
  m_id  NUMBER,
  m_nos int_list
)
NESTED TABLE m_nos STORE AS mytable_numbers;
INSERT ALL
  INTO mytable (m_id, m_nos) VALUES (1, int_list(1,2,3))
  INTO mytable (m_id, m_nos) VALUES (2, int_list())
  INTO mytable (m_id, m_nos) VALUES (3, int_list(2,4))
SELECT 1 FROM DUAL;
3 rows affected

SELECT mt.m_id,
       mtn.COLUMN_VALUE
FROM   mytable mt
       LEFT OUTER JOIN TABLE(mt.m_nos) mtn
       ON 1 = 1;


M_ID
COLUMN_VALUE


1
1

1
2

1
3

2
null

3
2

3
4


SELECT mt.m_id,
       LISTAGG(mtn.COLUMN_VALUE, ',') WITHIN GROUP (ORDER BY mtn.COLUMN_VALUE)
         AS m_nos
FROM   mytable mt
       LEFT OUTER JOIN TABLE(mt.m_nos) mtn
       ON 1 = 1
GROUP BY mt.m_id;


M_ID
M_NOS


1
1,2,3

2
null

3
2,4