title: Unpivot Multiple Columns in Oracle SQL
body: <p>I have a requirement to unpivot a table similar to the table below:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>I need the format to be something like below:    </p>
  
  <pre><code>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                                                
  </code></pre>
  
  <p>This is what i've done so far:</p>
  
  <pre><code>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));
  </code></pre>
  
  <p><strong>QUERY OUTPUT</strong></p>
  
  <pre><code>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                                   
  </code></pre>
  
  <p>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?</p>