I have a requirement to unpivot a table similar to the table below:
create TABLE dummy_x
(
EMP_NAME VARCHAR2(100)
, EMP_NUMBER VARCHAR2(100)
, PAYROLL_NAME VARCHAR2(100)
, PAYROLL_ID NUMBER
, JOB_TITLE VARCHAR2(100)
, JOB_TITLE_ID NUMBER
, LOCATION VARCHAR2(100)
, LOCATION_ID NUMBER
, NEW_PAYROLL_NAME VARCHAR2(100)
, NEW_PAYROLL_ID NUMBER
, NEW_JOB_TITLE VARCHAR2(100)
, NEW_JOB_TITLE_ID NUMBER
, NEW_LOCATION VARCHAR2(100)
, NEW_LOCATION_ID NUMBER
);
INSERT INTO dummy_x (EMP_NAME, EMP_NUMBER, PAYROLL_NAME, PAYROLL_ID, JOB_TITLE, JOB_TITLE_ID, LOCATION, LOCATION_ID, NEW_PAYROLL_NAME, NEW_PAYROLL_ID, NEW_JOB_TITLE, NEW_JOB_TITLE_ID, NEW_LOCATION, NEW_LOCATION_ID)
VALUES ('MISIP', '111X', 'PAY1', 1, 'DEVELOPER', 2, 'PHIL', 3, 'PAYPHIL', 11, 'PHIL DEV', 22, 'MANILA PH', 33);
INSERT INTO dummy_x (EMP_NAME, EMP_NUMBER, PAYROLL_NAME, PAYROLL_ID, JOB_TITLE, JOB_TITLE_ID, LOCATION, LOCATION_ID, NEW_PAYROLL_NAME, NEW_PAYROLL_ID, NEW_JOB_TITLE, NEW_JOB_TITLE_ID, NEW_LOCATION, NEW_LOCATION_ID)
VALUES ('FHONS', '111Y', 'PAY2', 2, 'SUPPORT', 3, 'HONDURAS', 4, 'PAYHON', 55, 'HON SUP', 66, 'SP SULA HON', 77);
I need the format to be something like below:
EMP_NAME EMP_NUMBER DETAILS CURRENT_VALUE NEW_VALUE
--------- ------------ -------------- -------------- ----------
MISIP 111X PAYROLL_NAME PAY1 PAYPHIL
PAYROLL_ID 1 11
JOB_TITLE DEVELOPER PHIL DEV
JOB_TITLE_ID 2 22
LOCATION PHIL MANILA PH
LOCATION_ID 3 33
FHONS 111Y PAYROLL_NAME PAY2 PAYHON
PAYROLL_ID 2 55
JOB_TITLE SUPPORT HON SUP
JOB_TITLE_ID 3 66
LOCATION HONDURAS SP SULA HON
LOCATION_ID 4 77
This is what i've done so far:
SELECT EMP_NAME
, EMP_NUMBER
, Details
, current_value
FROM (SELECT EMP_NAME
, EMP_NUMBER
, PAYROLL_NAME
, cast(PAYROLL_ID as varchar2(100)) PAYROLL_ID
, JOB_TITLE
, cast(JOB_TITLE_ID as varchar2(100)) JOB_TITLE_ID
, LOCATION
, cast(LOCATION_ID as varchar2(100)) LOCATION_ID
, NEW_PAYROLL_NAME
, cast(NEW_PAYROLL_ID as varchar2(100)) NEW_PAYROLL_ID
, NEW_JOB_TITLE
, cast(NEW_JOB_TITLE_ID as varchar2(100)) NEW_JOB_TITLE_ID
, NEW_LOCATION
, cast(NEW_LOCATION_ID as varchar2(100)) NEW_LOCATION_ID
FROM dummy_x)
unpivot (current_value for Details in (PAYROLL_NAME
, PAYROLL_ID
, JOB_TITLE
, JOB_TITLE_ID
, LOCATION
, LOCATION_ID));
QUERY OUTPUT
EMP_NAME EMP_NUMBER DETAILS CURRENT_VALUE NEW_VALUE
--------- ------------ -------------- -------------- ----------
MISIP 111X PAYROLL_NAME PAY1
MISIP 111X PAYROLL_ID 1
MISIP 111X JOB_TITLE DEVELOPER
MISIP 111X JOB_TITLE_ID 2
MISIP 111X LOCATION PHIL
MISIP 111X LOCATION_ID 3
FHONS 111Y PAYROLL_NAME PAY2
FHONS 111Y PAYROLL_ID 2
FHONS 111Y JOB_TITLE SUPPORT
FHONS 111Y JOB_TITLE_ID 3
FHONS 111Y LOCATION HONDURAS
FHONS 111Y LOCATION_ID 4
How can i add the "New Value" Column data to this script and would it be possible to remove the duplicate data from the EMP_NAME and EMP_NUMBER columns?
Do the cell merging in your application code.
For new_value, try this:
SELECT EMP_NAME
, EMP_NUMBER
, Details
, current_value
, new_value
FROM (SELECT EMP_NAME
, EMP_NUMBER
, PAYROLL_NAME
, cast(PAYROLL_ID as varchar2(100)) PAYROLL_ID
, JOB_TITLE
, cast(JOB_TITLE_ID as varchar2(100)) JOB_TITLE_ID
, LOCATION
, cast(LOCATION_ID as varchar2(100)) LOCATION_ID
, NEW_PAYROLL_NAME
, cast(NEW_PAYROLL_ID as varchar2(100)) NEW_PAYROLL_ID
, NEW_JOB_TITLE
, cast(NEW_JOB_TITLE_ID as varchar2(100)) NEW_JOB_TITLE_ID
, NEW_LOCATION
, cast(NEW_LOCATION_ID as varchar2(100)) NEW_LOCATION_ID
FROM dummy_x)
unpivot ((current_value, new_value) for Details in (
(PAYROLL_NAME, NEW_PAYROLL_NAME) as 'PAYROLL_NAME'
, (PAYROLL_ID , NEW_PAYROLL_ID ) as 'PAYROLL_ID'
, (JOB_TITLE , NEW_JOB_TITLE ) as 'JOB_TITLE'
, (JOB_TITLE_ID, NEW_JOB_TITLE_ID) as 'JOB_TITLE_ID'
, (LOCATION , NEW_LOCATION ) as 'LOCATION'
, (LOCATION_ID , NEW_LOCATION_ID) as 'LOCATION_ID'
)
);