-
amazing problem
Hi Gurus,
I am sgoogling and askTom for quite a while and I cannot find a good solution for my kind of problem.
I have a table with a couple of rows, sometimes more sometimes less, the table will be created from a csv file using sqlloader.
My problem is:
PHP Code:
from this table
__________________________________________________________________________________________
| id | mon1 | mon2 | mon3 | mon4 | mon5 |
|_________________________________________________________________________________________
| 1 | 1 | 2 | 5 | 2 | 1 |
|_________________________________________________________________________________________
| 2 | 1 | 2 | 5 | 7 | 2 |
|_________________________________________________________________________________________
-->
all values should inserted into a new table: (these are routes from 1 point to another)
|Aid | mob1_id | mob2_id |
|_____________________________|
| 1 | 1 | 2 |
______________________________|
| 1 | 2 | 5 |
_______________________________
| 1 | 5 | 2 |
|_____________________________|
| 1 | 2 | 1 |
|_____________________________|
in words: just split each row in each colum and but it in the new table.
I have already written a lot of code but nothing works..
Maybe one of you had a similar problem.
Help or a tipp would really be great...I am getting mad...
THX a lot.
Petra.
-
Try this:
PHP Code:
Select id, mon1, mon2 From MyTable
UNION
Select id, mon2, mon3 From MyTable
UNION
Select id, mon3, mon4 From MyTable
UNION
Select id, mon4, mon5 From MyTable
Order by 1,2,3;
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Originally Posted by LKBrwn_DBA
Try this:
PHP Code:
Select id, mon1, mon2 From MyTable
UNION
Select id, mon2, mon3 From MyTable
UNION
Select id, mon3, mon4 From MyTable
UNION
Select id, mon4, mon5 From MyTable
Order by 1,2,3;
UNION ALL, surely?
-
Hello,
thanks for your hint, but it's not working the way I need it.
because the amount of values in the table1 is not always the same...sometimes there are 3, 5, or 7 values or more in a single row...
--------
Table1:
--------
1 mon1 mon2 mon5 mon2 mon1
2 mon1 mon2 mon3 mon4 mon6 mon1 mon2 mon1
3 mon1 mon5 mon1
---------
Table2: |
----------
1 mon1 mon2
1 mon2 mon5
1 mon5 mon2
1 mon2 mon1
2 mon1 mon2
2 mon2 mon3
2 mon3 mon4
2 mon4 mon6
2 mon6 mon2
2 mon2 mon1
then the union command does not work...
any other suggestions...
Thx...
Petra.
-
Originally Posted by petzi74
then the union command does not work...
why not?
-
a row contains sometimes 4, sometimes 5, or 3, 6,7,8 values.
As mentioned above this is not because of a bad design.
A short explanation:
these values are just objects of different routes - some routes have only 3 monizoring objects, another 4, or 5...etc.
As soon as 1 route failed - every 10 minutes a token is send - the result is the route where the token has been failed.
e.g. mon1 --> mon2 --> mon5 --> mon1
Petra.
-
That's amazing all right
Jeff Hunter
-
no amount of programming will overcome a bad design.
Jeff Hunter
-
Oh, thanks for that answer, but this is not based on a BAD Design.
-
Can you post the table creation statement and rows insert ?
That may help to understand the problem.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|