Topic: SQL*Plus and PL/SQL >> Help pls
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| karthikamir |
Posted: Jan 17, 2008 01:04:31 AM |
Total Post: 24
Joined: Aug, 2007
|
Hi,
i have a long table which also has 5 columns for state(like state1,state2..)and 5 city(city1.city2..) all are in same row against a emp id.now i want to split and send it to another table which have structure like
empid state city subid
so each emp id is repeated 5 times.
BUT 1 thing is the subid should be increamental(ofcourse it ranges from 1 to 5 only) if empid is same and start from 1 for next emp id.the total records is 25000 i duno how to break this long table and split the datas(the confusions is only subid other things i made through union query)to this table... help me pls.
thanks in advance |
|
|
 Nehru |
| Posted: Jan 17, 2008 04:10:45 AM |  |
|
Total Post: 45
Joined: Jul, 2007
|
Hi Karthik,
I dosen't under stand your requirement.
better to post clearly then only every one try and reply to you.
better to post some sample data.
Take care.
Thanks,
Nehru.
|
|
|
|
|
| Devesh |
| Posted: Jan 17, 2008 07:19:11 AM | |
|
Total Post: 29
Joined: Oct, 2005
|
if u have a table data like:
select * from long_tab;
EMPID STATE1 STATE2 STATE3 STATE4 STATE5 CITY1 CITY2 CITY3 CITY4 CITY5
emp1 s1 s2 s3 s4 s5 c1 c2 c3 c4 c5
emp2 ss1 ss2 ss3 ss4 ss5 cc1 cc2 cc3 cc4 cc5
then as per your requirement below query will help:
select ROW_NUMBER()
OVER (PARTITION BY empid order by empid) "Seq", empid,state1 State,city1 city from(
select empid,state1 ,city1 from long_tab
union
select empid,state2,city2 from long_tab
union
select empid,state3,city3 from long_tab
union
select empid,state4,city4 from long_tab
union
select empid,state5,city5 from long_tab);
it will display like:
Seq EMPID STATE CITY
1 emp1 s1 c1
2 emp1 s2 c2
3 emp1 s3 c3
4 emp1 s4 c4
5 emp1 s5 c5
1 emp2 ss1 cc1
2 emp2 ss2 cc2
3 emp2 ss3 cc3
4 emp2 ss4 cc4
5 emp2 ss5 cc5
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 272 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
