how can i split the table

Posted by Rajesh123 under Oracle on 8/17/2015 | Points: 10 | Views : 1871 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

How can i write the query below table.please any one rply ASAP
Person

ID      name          email            phone    child1    child2  child3
1      mahesh         mahi@gmail.com   1234      sam       joe      job

above table is main table.
but i have spilt the data to two tables like below table.
----------------------------------------------------------

Id    name   email               phone
1    mahesh  mahi@gmail.com     1234

Id   seqno    child
1     1        sam
1     2        joe
1     3        job

rajesh.plsql123@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Shreedar on: 8/17/2015 [Member] Starter | Points: 25

0	Hi Rajesh. What is your requirement? You have two tables so now which query you want. can you elaborate more? Regards, Sridhar Thota. Regards Sridhar Thota. www.dotnet-sridhar.blogspot.com Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Amatya on: 8/17/2015 [Member] Silver | Points: 25

0	Didnot get you Feel free to share informations. mail Id ' adityagupta200@gmail.com Thanks Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 8/17/2015 [Member] [MVP] Platinum | Points: 25

0	refer this link http://forums.asp.net/t/1946711.aspx?Help+splitting+comma+seperated+column+in+to+rows http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle simulate same to your scenario... Try this with temp as ( select ID, child1+','+child2+','+child3 as Childs from Person ) SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) as seqno ,trim(regexp_substr(str, '[^,]+', 1, level)) str FROM (SELECT ID, Childs FROM temp) t CONNECT BY instr(Childs, ',', 1, level - 1) > 0 order by ID Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

refer this link
http://forums.asp.net/t/1946711.aspx?Help+splitting+comma+seperated+column+in+to+rows
http://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle

simulate same to your scenario...

Try this

with temp as  (
       select ID, child1+','+child2+','+child3 as Childs
       from Person
     )
SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) as seqno
	,trim(regexp_substr(str, '[^,]+', 1, level)) str
  FROM (SELECT ID, Childs  FROM temp) t
CONNECT BY instr(Childs, ',', 1, level - 1) > 0
order by ID

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Rajesh123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajesh123 on: 8/17/2015 [Member] Starter | Points: 25

0	Hi am try to above query but did not work. i try my level best but its not work properly. create table person1(p_id number(2),p_name varchar2(10),email varchar2(10),phone number(5)); insert into person1 values (&p_id,'&p_name','&eamil',&phone); P_ID P_NAME EMAIL PHONE CHILD1 CHILD2 CHILD3 1 rar rah@mail 2525 rajesh suresh balaji select * from person1 ; delete from person1 where p_id='23'; alter table person1 add child1 varchar2(10) add child2 varchar2(10) add child3 varchar2(10); update person1 set child1='rajesh',child2='suresh',child3='balaji' where p_id='1'; with temp as ( select p_id,child1+','+child2+','+child3 as Childs from person1 ) SELECT p_id,ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY p_id) as seqno ,trim(regexp_substr(str, '[^,]+', 1, level)) str FROM (SELECT p_id, Childs FROM temp) t CONNECT BY instr(Childs, ',', 1, level - 1) > 0 ORDER BY p_id; rajesh.plsql123@gmail.com Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

Hi
am try to above query but did not work.
i try my level best but its not work properly.

create table person1(p_id number(2),p_name varchar2(10),email varchar2(10),phone number(5));

insert into person1 values (&p_id,'&p_name','&eamil',&phone);

P_ID  P_NAME   EMAIL     PHONE CHILD1 CHILD2 CHILD3      
1     rar     rah@mail 	 2525	 rajesh suresh balaji

select * from person1

;

delete from person1 where p_id='23';

alter table person1 add child1 varchar2(10) add child2 varchar2(10) add child3 varchar2(10);

update person1 set child1='rajesh',child2='suresh',child3='balaji' where p_id='1';

with temp as  (
       select p_id,child1+','+child2+','+child3 as Childs
       from person1
     )
SELECT p_id,ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY p_id) as seqno
        ,trim(regexp_substr(str, '[^,]+', 1, level)) str
  FROM (SELECT p_id, Childs  FROM temp) t
CONNECT BY instr(Childs, ',', 1, level - 1) > 0
ORDER BY p_id;

rajesh.plsql123@gmail.com

Rajesh123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/18/2015 [Member] [MVP] Platinum | Points: 25

0	am sorry, i doesn't have Oracle to test this... are you getting any error ? Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Rajesh123 on: 8/18/2015 [Member] Starter | Points: 25

0	Hi bandi getting below error msg SQL> with temp as ( 2 select p_id,child1+','+child2+','+child3 as Childs 3 from person1 4 ) 5 SELECT p_id,ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY p_id) as seqno 6 ,trim(regexp_substr(str, '[^,]+', 1, level)) str 7 FROM (SELECT p_id, Childs FROM temp) t 8 CONNECT BY instr(Childs, ',', 1, level - 1) > 0 9 ORDER BY p_id; ,trim(regexp_substr(str, '[^,]+', 1, level)) str * ERROR at line 6: ORA-00904: "STR": invalid identifier rajesh.plsql123@gmail.com Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

Hi bandi
getting below error msg

SQL> with temp as  (
  2  select p_id,child1+','+child2+','+child3 as Childs
  3  from person1
  4  )
  5  SELECT p_id,ROW_NUMBER() OVER(PARTITION BY p_id ORDER BY p_id) as seqno
  6  ,trim(regexp_substr(str, '[^,]+', 1, level)) str
  7  FROM (SELECT p_id, Childs  FROM temp) t
  8  CONNECT BY instr(Childs, ',', 1, level - 1) > 0
  9  ORDER BY p_id;
,trim(regexp_substr(str, '[^,]+', 1, level)) str

*
ERROR at line 6:
ORA-00904: "STR": invalid identifier

rajesh.plsql123@gmail.com

Rajesh123, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/20/2015 [Member] [MVP] Platinum | Points: 25

0	use different column alias instead of str .... and try Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Rajesh123, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts