amazing problem

[petzi74]

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:

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.

[LKBrwn_DBA]

Try this:

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; 

[marist89]

That's amazing all right

[slimdave]

Try this:

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?

[petzi74]

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.

[marist89]

no amount of programming will overcome a bad design.

[petzi74]

Oh, thanks for that answer, but this is not based on a BAD Design.

[slimdave]

then the union command does not work...

why not?

[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


Petra.

[LKBrwn_DBA]

Ok try this one (assuming there are no gaps (nulls) in the middle of route):

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.