-
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
-
That's amazing all right
Jeff Hunter
-
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.
-
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.
-
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.
-
Ok try this one (assuming there are no gaps (nulls) in the middle of route):
PHP Code:
Select Id, Mon1, Mon2 From Mytable
Union
Select Id, Mon2, Mon3 From Mytable
Where Mon3 Is Not Null
Union
Select Id, Mon3, Mon4 From Mytable
Where Mon4 Is Not Null
Union
Select Id, Mon4, Mon5 From Mytable
Where Mon5 Is Not Null
Order By 1,2,3;
PS: Union all will make no difference unless routes are duplicate.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
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
|