Columns of My Unpivot Query Results not Where I Want Them to Be

Question

I wrote an SQL query that transforms phone numbers from being listed in three separate columns (Pri_Phn_No, Alt_Phn_No, Cell_Phn_No) - into one column of phone numbers AND one column of phone types

The problem is that the query results display the DATE_CREATED, and DATE_MODIFIED columns before the phone number and phone type columns. I will like them to display AFTER those columns. Nothing I've tried has worked so far.

**If I explicitly add the fields in the order I want - to the Unpivot part of the script: user_id, phone_type, phone_number, date_created, date_modified, I get the following error message

ORA-OO904: "date_modified": invalid identifier
00904. 0000 - "%s: invalid identifier"**

The Query

CREATE Table UserPhoneNo nologging as

SELECT USER_ID, PRI_PHN_NO, ALT_PHN_NO, CELL_PHN_NO, DATE_CREATED, DATE_MODIFIED
FROM Address_history;

SELECT * FROM UserPhoneNo
UNPIVOT INCLUDE NULLS(
    (phone_number)  -- unpivot_clause
    FOR phone_type --  unpivot_for_clause
    IN ( -- unpivot_in_clause
        PRI_PHN_NO AS 'Primary', 
        ALT_PHN_NO AS 'Secondary', 
        CELL_PHN_NO AS 'Tertiary'
    )
)
order by USER_ID;

drop Table UserPhoneNo;

Query Results

Sample Query Data

CREATE TABLE Address_History (
    USER_ID NUMBER(9) NOT NULL,
    PRI_PHN_NO VARCHAR2(50) NOT NULL,
    ALT_PHN_NO VARCHAR2(50) NULL,
    CELL_PHN_NO VARCHAR2(50) NULL,
    DATE_CREATED DATE NOT NULL,
    DATE_MODIFIED DATE NULL
);

Insert into Address_History 
(USER_ID,PRI_PHN_NO,ALT_PHN_NO,CELL_PHN_NO,DATE_CREATED,DATE_MODIFIED) values (475,'2191234567',2197654321,null,to_date('04-NOV-06','DD-MON-RR'),to_date('11-AUG-14','DD-MON-RR'));

Insert into Address_History
(USER_ID,PRI_PHN_NO,ALT_PHN_NO,CELL_PHN_NO,DATE_CREATED,DATE_MODIFIED) values (467,'3191234567',null,null,to_date('04-NOV-06','DD-MON-RR'),to_date('03-APR-08','DD-MON-RR'));

Insert into Address_History 
(USER_ID,PRI_PHN_NO,ALT_PHN_NO,CELL_PHN_NO,DATE_CREATED,DATE_MODIFIED) values (468,'4191234567',4197654321,null,to_date('04-NOV-06','DD-MON-RR'),null);

Insert into Address_History
 (USER_ID,PRI_PHN_NO,ALT_PHN_NO,CELL_PHN_NO,DATE_CREATED,DATE_MODIFIED) values (469,'5191234567',null,null,to_date('04-NOV-06','DD-MON-RR'),to_date('20-JAN-10','DD-MON-RR'));

Insert into Address_History 
(USER_ID,PRI_PHN_NO,ALT_PHN_NO,CELL_PHN_NO,DATE_CREATED,DATE_MODIFIED) values (471,'6191234567',null,null,to_date('04-NOV-06','DD-MON-RR'),to_date('19-MAY-08','DD-MON-RR'));

Insert into Address_History 
(USER_ID,PRI_PHN_NO,ALT_PHN_NO,CELL_PHN_NO,DATE_CREATED,DATE_MODIFIED) values (473,'7191234567',7197654321,null,to_date('04-NOV-06','DD-MON-RR'),to_date('20-JAN-10','DD-MON-RR'));

Answer 1

This is the correct way to write the query to see the desired columns where you want them to be:

SELECT USER_ID, phone_type, phone_number, DATE_CREATED, DATE_MODIFIED FROM (SELECT USER_ID, PRI_PHN_NO, ALT_PHN_NO, CELL_PHN_NO, DATE_CREATED, DATE_MODIFIED FROM Address_history)

UNPIVOT INCLUDE NULLS(

phone_number -- unpivot_clause

FOR phone_type -- unpivot_for_clause

IN ( -- unpivot_in_clause

PRI_PHN_NO AS 'Primary', 

ALT_PHN_NO AS 'Secondary', 

CELL_PHN_NO AS 'Tertiary'

) )

order by USER_ID;