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

Uday_N

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

Solomon Yakobson

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.

Frank Kulash

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

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.

Solomon Yakobson

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

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

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

@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

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

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