how can i split the table

Posted by Rajesh123 under Oracle on 8/17/2015 | Points: 10 | Views : 568 | Status : [Member] | Replies : 7
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


Responses

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

Up
0
Down
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

Up
0
Down
Didnot get you

Feel free to share informations 9731764134
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response