Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Question Unanswered: Moving Row Data to Columns

    Nead to create an SQL using the following data (sample).

    Currently have data in the following format:

    select Unit No, Work Order Date from data order by Unit No, Worl Order Date..

    Unit No Work Order Date
    1111 1/1/2008
    1111 3/1/2008
    1222 9/3/2009
    1333 2/1/2008
    1444 6/4/2008
    1444 10/4/2008
    ..........

    Would like the following result:

    Unit No Work Order Next Work Order
    1111 1/1/2008 3/1/2008
    1111 3/1/2008 NULL
    1222 9/3/2009 NULL
    1333 2/1/2008 NULL
    1444 6/4/2008 10/4/2008
    1444 10/4/2008 NULL
    ...........

    Trying use the 'Unit No' unique where the 'Next Work Order' appears.

    Thanks for the ideas...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It would be helpful if you provided DDL for tables involved.
    It would be helpful if you provided DML for test data.

    LAG
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    Troubles using the 'LAG'. It does not seem to create the NULL as the next 'Unit No' is selected. The line used was "lag (work order no,1,0) over (order by unit no, work order no) as next work order no"... Thanks..

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I apologize & ask your forgiveness.
    My crystal Ball is in the shop for repair.
    Since I am not standing behind you, I can't see what you are seeing.

    Too bad for me that CUT & PASTE are broken for you.
    Since you provided no useful details, you can enjoy your mystery by yourself.

    P.S.
    Just curious, have you ever used the NVL() function?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    hey if i remember correct you have asked the same question in sql server forum .

    are you making us to do your homework ??

  6. #6
    Join Date
    Nov 2009
    Posts
    4
    Didn't realize this was the same area. Still looking for a solution as noted above, handling this manually today and would be great to automate. Will have to check in with SQL Server Forum mentioned... Any help would be useful...

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by West4961 View Post
    Troubles using the 'LAG'. It does not seem to create the NULL as the next 'Unit No' is selected. The line used was "lag (work order no,1,0) over (order by unit no, work order no) as next work order no"... Thanks..
    Just two remarks. Firstly, LAG gives values from rows prior the current one. Maybe you will need to use LEAD function instead. Also, why did you set the last parameter to 0, as your expected output is supposed to contain NULLs?
    Additionally, have a look at PARTITION BY option in OVER clause. Maybe you want to place (= move from ORDER BY clause) "unit no" (I wonder how Oracle treats multi word identifiers) there.

    And, be prepared, that SQL Server probably will not be able to run this code (although it might introduced similar concept, I am not familiar with it).

    [Edit: Precised the hint about using OVER]
    Last edited by flyboy; 11-16-09 at 22:27.

  8. #8
    Join Date
    Nov 2009
    Posts
    4
    Perfect!!! Adding the 'partition by' option kept the 'unit numbers' together so the NULLs easitly stayed together. It worked perfectly. Planned to apply on a couple SQLs currently working with.... Thanks for all your ideas!!!

Tags for this Thread

Posting Permissions

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