Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unanswered: Rearranging values in temp table

    Hello everyone

    I have a temp table that looks like this (as a result of two seperate inserts into it):

    RowNumber starttime endtime
    ----------- ----------- -----------
    1 l 0 l NULL
    2 l 360 l NULL
    3 l 720 l NULL
    4 l 1020 l NULL
    5 l 1380 l NULL
    6 l NULL l 180
    7 l NULL l 540
    8 l NULL l 900
    9 l NULL l 1200
    10 l NULL l 1440

    I need some help getting it to look like this (with No NULL cell values)!

    RowNumber starttime endtime
    ----------- ----------- -----------
    1 l 0 l 180
    2 l 360 l 540
    3 l 720 l 900
    4 l 1020 l 1200
    5 l 1380 l 1440

    PLEASE help I have been looking at this for the past 3 days and can't figure it out!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just hate RFH problems like this! Oh well...

    Has your instructor covered correlated sub-queries yet?

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select t1.starttime, t2.endtime
    from (select RowNumber, starttime from tbl where endtime is null) t1
    inner join (select RowNumber endtime from tbl where starttime is null) t2
    on t1.RowNumber = t2.RowNumber - 5
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    How do you know which ones belong to which? Does the table look exactly like you just said? After 10 are there any valus with the second column filled and the third column NULL? How did the data get like this in the first place??? Are you going to be able to fix that, or will this be ongoing?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jul 2004
    Posts
    53

    hmm...

    Thank you for your responses!

    I tried your suggestion rdjabarov:

    select t1.starttime, t2.endtime
    from (select RowNumber, starttime from #end where endtime is null) t1
    inner join (select RowNumber endtime from #end where starttime is null) t2
    on t1.RowNumber = t2.RowNumber - 5

    and I get an "Invalid column name 'RowNumber'." (RowNumber is a valid column in the temp table).

    The temp table became this way because I wanted to store a result set from one query into one column of a temp table, and then store another result set from another query into the second column of the temp table.

    The number of result sets for each query will always be the same. For example if I have 14 rows, 1 will link to 8, 2 will link to 9, 3 to 10, etc.

    I did try another method: I created one temp table that contained the start times (with RowNumber 1-5), I then created another temp table that contained only the endtimes (with RowNumber 1-5) and joined both tables on the RowNumber and then inserted those values into another temp table (3 temp tables in total!).

    I would like to use rdjabarov's solution (with 1 temp table) for improved performance...can you tell me why I am getting the:

    "Invalid column name 'RowNumber'." error?

    THANK YOU!!

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Oooooops...

    select t1.starttime, t2.endtime
    from #end t1
    inner join #end t2
    on t1.RowNumber = t2.RowNumber - 5
    and t2.starttime is null
    and t1.endtime is null
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jul 2004
    Posts
    53

    Thank You!!

    GREAT! That worked! I am indebted to you rdjabarov!!!

    Thank you very much for responding as quickly as you did!!!

Posting Permissions

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