根據初始表列中的值創建基於表的表

我有一張像這樣的桌子：

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

如何從該表創建一個新表（或視圖），以便如果“地址”列包含分隔符兩側的不同值，則使用 Oracle PL/SQL 將兩行與所有其他數據一起插入新表? 對於此範例，結果應為：

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

好吧，這可能會解決您的問題。

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> 

引用自：https://dba.stackexchange.com/questions/132784