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:
in words: just split each row in each colum and but it in the new table.
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 |
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.
Select id, mon1, mon2 From MyTable
Select id, mon2, mon3 From MyTable
Select id, mon3, mon4 From MyTable
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
UNION ALL, surely?
Originally Posted by LKBrwn_DBA
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...
1 mon1 mon2 mon5 mon2 mon1
2 mon1 mon2 mon3 mon4 mon6 mon1 mon2 mon1
3 mon1 mon5 mon1
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...
Originally Posted by petzi74
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
That's amazing all right
no amount of programming will overcome a bad design.
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.
Click Here to Expand Forum to Full Width