Altering column data type

Question

How can I alter a column of number to a list of numbers.

In myTable, m_no is an integer. Now I wish to change m_no column to a list of integers without loosing the currently stored values.

CREATE TABLE myTable (
    m_id,
    m_no
);

Answer 1

Don't try to store a list of numbers in a column (especially not as comma-separated values).

What you should do is create another table:

CREATE TABLE myTable (
    m_id NUMBER PRIMARY KEY
);

CREATE TABLE myTable_numbers (
    m_id REFERENCES mytable(m_id),
    m_no NUMBER(10,0)
);

Then, when you want to get the list of numbers for an id you can use a JOIN:

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

Which, for the sample data:

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;

Outputs:

M_ID	M_NO
1	1
1	2
1	3
3	2
3	4
2	null

Or, if you want a comma-separated list of numbers (for display purposes) then JOIN and aggregate:

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;

Which outputs:

M_ID	M_NOS
1	1,2,3
2	null
3	2,4

---

If you want to modify the existing table to use two tables then:

1. Create the myTable_numbers table using the DDL statement above (without the referential constraint).
2. Copy the data using INSERT INTO mytable_numbers (m_id, m_no) SELECT m_id, m_no FROM my_table;
3. Drop the m_no column from mytable.
4. Delete the duplicate m_id values from myTable.
5. Add a primary key or unique constraint on mytable.m_id.
6. Add the referential constraint on mytable_numbers.m_id to mytable.m_id.

---

If you really want a list of numbers in a column (don't) then use a nested table:

CREATE TYPE int_list IS TABLE OF NUMBER(10,0);

CREATE TABLE myTable (
  m_id  NUMBER,
  m_nos int_list
)
STORE m_nos AS mytable_numbers;

Then you can insert the same data:

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;

And use the queries:

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

or

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;

giving the same output as above.

fiddle