Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: Repeating a task

    OK gentlemen, here's another one.

    How do I repeat a task a few times over until the condition is met.

    I have two tables(table1 and table2). Table 1 consists of an ID column and a number of user data columns. Table2 consists of one ID coloumn, one table1ID column and one user data column.

    What I need to do is to take the data fields lying horizontally in table1, and stack them up vertically into table2 consecutively, while keeping their association with the appropriate id in table1.


    It would look something like this:


    table1
    --------------------------------------------------------------
    id data1 data2 data3 data4
    --------------------------------------------------------------
    1 appple orange melon kiwi
    2 green red blue yellow
    3 ford honda bmw mazeratti
    4 Mary stacy Jane Sharon



    table2
    ---------------------------------------
    id table1id data
    ---------------------------------------
    1 1 apple
    2 1 orange
    3 1 melon
    4 1 kiwi
    5 2 green
    6 2 red
    7 2 blue
    . . .
    . . .
    . . .
    15 4 Jane
    16 4 Sharon


    Any ideas about how to do that?

    Thanks
    Last edited by Tom Bombadill; 11-05-03 at 23:11.

  2. #2
    Join Date
    Oct 2003
    Posts
    13
    Well, the tables didn't come out the way I intended. But I hope you get the idea.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Repeating a task

    table1
    --------------------------------------------------------------
    id data1 data2 data3 data4
    --------------------------------------------------------------
    1 appple orange melon kiwi
    2 green red blue yellow
    3 ford honda bmw mazeratti
    4 Mary stacy Jane Sharon



    table2
    ---------------------------------------
    id table1id data
    ---------------------------------------
    1 1 apple
    2 1 orange
    3 1 melon
    4 1 kiwi


    I didn't test it but something like this whould work :

    Insert Into Table2 (table1id,data)
    (Select Id, Data1 From Table1
    Union
    Select Id, Data2 From Table1
    Union
    Select Id, Data3 From Table1
    Union
    Select Id, Data4 From Table1
    Order by 1)

    Id form Table2 should be an auto-incremental id

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Repeating a task

    Insert Into Table2 (table1id,data)
    (Select Id, Data1 From Table1
    Union
    Select Id, Data2 From Table1
    Union
    Select Id, Data3 From Table1
    Union
    Select Id, Data4 From Table1
    )

    Without the "order by" it works
    but the data is not inserted in the good order
    so if it matters you could dump the ordered data into a temp table
    and then insert the rows from that temp table into Table2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •