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

ddloracleplsqlregular expressionview

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 |
+----+--------+---------+

Best Answer

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>

Related Solutions

Oracle SQL – Return One Row from Table with Similar Values

You can use the EXCEPT keyword:

CREATE TABLE #T1(c1 char(1), c2 int)
go

INSERT INTO #T1 Values('A',1)
INSERT INTO #T1 Values('A',2)
INSERT INTO #T1 Values('A',3)
INSERT INTO #T1 Values('A',4)
INSERT INTO #T1 Values('B',1)
INSERT INTO #T1 Values('B',2)
INSERT INTO #T1 Values('C',1)
INSERT INTO #T1 Values('C',3)
INSERT INTO #T1 Values('C',4)
INSERT INTO #T1 Values('D',2)
GO

SELECT c1 FROM  #T1
EXCEPT 
SELECT c1 FROM #t1 WHERE c2 = 3

DROP table #T1

Postgresql – How to Create a View and Extract Day-Month from Epoch Date

The answer is, like you already mentioned in the PostgreSQL documentation. An example is in the reply of Postgres: how to convert from unix epoch to date?

select timestamp with time zone 'epoch' + Day_start * interval '1 second'
from   "MainTable";

or simpler (thanks to @a_horse_with_no_name):

select to_timestamp(day_start)
from   "MainTable";

Use the to_char(timestamp, 'DD-MM') to get the day and month. The result to put in your CREATE VIEW would be something like:

select to_char(timestamp with time zone 'epoch' + Day_start * interval '1 second',
               'DD-MM') AS index;
from   "MainTable";

If you want the whole date as DATE type then use:

select (timestamp with time zone 'epoch' + Day_start * interval '1 second')::date AS index;
from   "MainTable";

Best Answer

Related Solutions

Oracle SQL – Return One Row from Table with Similar Values

Postgresql – How to Create a View and Extract Day-Month from Epoch Date

Related Question