Create a table based on a table depending on the values in the initial table column

Question

I have a table like:

+----+--------+----------------+
| id | name | address |
+----+--------+----------------+
| 1 | Ivan | Moscow,Moscow |
| 2 | Petr | Samara,Samara |
| 3 | Grigor | Moscow,Korolev |
+----+--------+----------------+

How can I create a new table (or view) from this table, so that if the 'address' column contains the different values on the sides of the separator, insert two rows into the new table with all other data using Oracle PL/SQL? For this example the result should be:

+----+--------+---------+
| id | name | address |
+----+--------+---------+
| 1 | Ivan | Moscow |
| 2 | Petr | Samara |
| 3 | Grigor | Moscow |
| 3 | Grigor | Korolev |
+----+--------+---------+

Answer 1

Well this may solve your problem.

SQL> desc tbl1
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 ID NUMBER
 NAME VARCHAR2(20)
 ADDRESS VARCHAR2(20)
SQL> select * from tbl1;
 ID NAME ADDRESS
---------- -------------------- --------------------
 1 Ivan Moscow,Moscow
 2 Peter Samara,Samara
 3 Grigor Moscow,Korolev
 4 John Moscow,Moscow
SQL> create or replace view vtest as
select id, name, substr(address,1,instr(address,',')-1) address from tbl1
union
select id, name, substr(address,instr(address,',')+1, length(address)) address from tbl1;
View created.
SQL> select * from vtest;
 ID NAME ADDRESS
---------- -------------------- --------------------
 1 Ivan Moscow
 2 Peter Samara
 3 Grigor Korolev
 3 Grigor Moscow
 4 John Moscow
SQL>