create table xx
(id number,
c_num varchar2(100),
validity_date date,
contract_no varchar2(30),
name1 varchar2(30),
name2 varchar2(50));
insert into xx values(100,6789,sysdate,'C100','xyz','abc');
insert into xx values(101,6789,sysdate,'C100','xyz','abc');
insert into xx values(102,6789,sysdate,'C100','xyz','abc');
insert into xx values(103,6789,sysdate,'C100','xyz','abc');
insert into xx values(104,6789,sysdate+1,'C101','kljio','lkjllk');
insert into xx values(105,6789,sysdate+2,'C101','kljio','lkjllk');
insert into xx values(106,6789,sysdate+3,'C101','uijhu','ljopj');
insert into xx values(107,6789,sysdate,'C102','khouh','uoikj');
--Table-1 (If all columns has same data for same CONTRACT_NO- It should insert only one row into Table1 with ID's separted with delimiter :)
ID,c_num,Validity_date,contract_id,name1,name2
100:101:102:103,6789,07-OCT-17,C100,xyz,abc
107,6789,07-OCT-17,C102,khouh,uoikj
--Table-2 (If the data in any of the columns is different for the same CONTRACT_NO- It should insert into Table2)
104,6789,08-OCT-17,C101,kljio,lkjllk
105,6789,09-OCT-17,C101,kljio,lkjllk
106,6789,10-OCT-17,C101,uijhu,ljopj
--Table-2 (If the data in any of the columns is different for the same CONTRACT_NO- It should insert into Table2)
104,6789,08-OCT-17,C101,kljio,lkjllk
105,6789,09-OCT-17,C101,kljio,lkjllk
106,6789,10-OCT-17,C101,uijhu,ljopj
Gurus- Please help me on the above scenario, based on the contract_no, the data should be inserted into 2 different tables.