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>