Forum Stats

  • 3,750,348 Users
  • 2,250,159 Discussions
  • 7,866,944 Comments

Discussions

display old and new values for n values of a column in a table

Uday_N
Uday_N Member Posts: 294 Bronze Badge

Hi All,

I have a table . I will update the table with the new value . I need to display old and new values as column. I tried PIVOT but since my entries are n in number i can't give definite values in FOR IN () in PIVOT .


EG:

CREATE TABLE TAB_A (ID NUMBER, EMP_ID NUMBER,NAME VARCHAR2(10),SALARY NUMBER);


INSERT INTO TAB_A (1,123,'Mike',1000);


INSERT INTO TAB_A (2,124,'Pete',2000);


INSERT INTO TAB_A (3,125,'Kevin',3000);


INSERT INTO TAB_A (4,123,'Mike',3000);


INSERT INTO TAB_A (5,124,'Pete',4000);


I need to display NAME,EMP_ID,OLD_SALARY,NEW_SALARY .


In real time , SALARY and EMP_ID are in definite not concluded to 3 values as mentioned above so I am not able to use FOR IN () . Please kind your advise


Regards,

Uday

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,654 Black Diamond
    Accepted Answer
    SELECT  NAME,
            EMP_ID,
            SALARY OLD_SALARY,
            LEAD(SALARY) OVER(PARTITION BY EMP_ID ORDER BY ID) NEW_SALARY
      FROM  TAB_A
      ORDER BY EMP_ID,
               ID
    /
    
    NAME           EMP_ID OLD_SALARY NEW_SALARY
    ---------- ---------- ---------- ----------
    Mike              123       1000       3000
    Mike              123       3000
    Pete              124       2000       4000
    Pete              124       4000
    Kevin             125       3000
    
    SQL>
    

    SY.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond

    Hi, @Uday_N

    Thanks for posting the sample data. Don't forget to post the exact results you want from the given data and an explanation of how you get those results from that data (e.g., how do you know if two rows refer to the same person, and which one is newer than the other).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,887 Red Diamond

    Hi, @Uday_N

    Would you like to get answers that work? Make sure the CREATE TABLE and INSERT statements you post work, too. Test (and, if necessary, fix) your statements before you post them.

    Uday_N
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,654 Black Diamond
    Accepted Answer
    SELECT  NAME,
            EMP_ID,
            SALARY OLD_SALARY,
            LEAD(SALARY) OVER(PARTITION BY EMP_ID ORDER BY ID) NEW_SALARY
      FROM  TAB_A
      ORDER BY EMP_ID,
               ID
    /
    
    NAME           EMP_ID OLD_SALARY NEW_SALARY
    ---------- ---------- ---------- ----------
    Mike              123       1000       3000
    Mike              123       3000
    Pete              124       2000       4000
    Pete              124       4000
    Kevin             125       3000
    
    SQL>
    

    SY.

  • Uday_N
    Uday_N Member Posts: 294 Bronze Badge

    Hi Frank,

    I apologize for the error caused .


    My desired o/p is :


    emp_id name old_salary new_salary

    123 Mike 1000 3000

    124 Pete 2000 4000


    Here in real time emp_id and salary may not be fixed to a few values .


    Sorry once again .


    Regards,

    Uday

  • Uday_N
    Uday_N Member Posts: 294 Bronze Badge

    Hi Solomon,

    @Solomon Yakobson Thanks for your answer. It worked . Thanks once again .

    @Frank Kulash - I aplogize for my mistake . Sorry for it.


    Regards,

    Uday

  • OTDeveloper
    OTDeveloper Member Posts: 20 Blue Ribbon

    @Uday_N

    How about the scenario where an employee salary is updated more than once like the ones highlighted in bold below?

    How would like to have your output in this case?

    INSERT INTO TAB_A (1,123,'Mike',1000);

    INSERT INTO TAB_A (2,124,'Pete',2000);

    INSERT INTO TAB_A (3,125,'Kevin',3000);

    INSERT INTO TAB_A (4,123,'Mike',3000);

    INSERT INTO TAB_A (5,124,'Pete',4000);

    INSERT INTO TAB_A (6,123,'Mike',5000);

    Regards,

    Sreekanth

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,654 Black Diamond

    What difference does it make?

    SELECT  NAME,
            EMP_ID,
            SALARY OLD_SALARY,
            LEAD(SALARY) OVER(PARTITION BY EMP_ID ORDER BY ID) NEW_SALARY
      FROM  TAB_A
      ORDER BY EMP_ID,
               ID
    /
    NAME           EMP_ID OLD_SALARY NEW_SALARY
    ---------- ---------- ---------- ----------
    Mike              123       1000       3000
    Mike              123       3000       5000
    Mike              123       5000
    Pete              124       2000       4000
    Pete              124       4000
    Kevin             125       3000
    
    6 rows selected.
    
    SQL>
    

    As you can see, Mike's salary was 1000 and was bumped up to 3000. Then his 3000 salary was bumped up to 5000. Next time his salary changes you will see one more row with old salary 500 and nes salary what ever it will be.

    SY.

  • OTDeveloper
    OTDeveloper Member Posts: 20 Blue Ribbon

    Hi SY,

    The query will correctly return all the changes as you mentioned.

    But I was trying to find out what exactly the OP wants in this case - a) Should we display all the changes made to an employee's salary or b) should we display the latest change that was made.

    I did not want to assume and hence asked the question.

    Regards,

    Sreekanth

    Uday_N