Unpivot Query with Multiple Columns

Question

I have the Table (EMP_EIT_EXT) below:

EMPLOYEE_NUMBER Next Academic Rank  Max Academic Rank   Max Score   Academic Rank   Promotion Rank  ACADEMIC_RANK_START_DATE ACADEMIC_RANK_END_DATE  Income_code Date(YYYY) Date(MON)   Date(DD)    Amount  Tax?    INCOME_START_DATE   INCOME_END_DATE
--------------- ------------------  ------------------  ----------  -------------   --------------  ------------------------ ------------------      ----------- ---------  ---------   ---------   ------  -----   -----------------   -------------------
33333           Professor           Y                   10.1        Ass.Professor   Y               01-JAN-2017              31-DEC-4712             P           2017       JAN         01          10000   Tax     01-JAN-2017         31-DEC-4712
44444           Ass.Professor       N                   9.7         Student         N               01-JAN-2017              31-DEC-4712             R           2017       JAN         17          50000   Non-Tax 01-JAN-2017         31-DEC-4712

How can I Unpivot this to make it Vertical in Orientation like below?

EMPLOYEE_NUMBER     EXTRA_INFORMATION       INFORMATION_CATEGORY    INFORMATION1    INFORMATION2    INFORMATION3
------------------- ---------------------   ----------------------  --------------  -------------   ------------
33333               Academic Rank           Next Academic Rank      Professor       Y               10.1        
33333               Academic Rank           Academic Rank           Ass.Professor   N               Y           
33333               Academic Rank           Effective Start Date    01-JAN-2017     (NULL)          (NULL)      
33333               Academic Rank           Effective End Date      31-DEC-4712     (NULL)          (NULL)      
33333               Alien Income Forecast   Income_code             P               (NULL)          (NULL)      
33333               Alien Income Forecast   Date                    2017            01              JAN         
33333               Alien Income Forecast   Amount                  10000           Tax             (NULL)      
33333               Alien Income Forecast   Effective Start Date    01-JAN-2017     (NULL)          (NULL)      
33333               Alien Income Forecast   Effective End Date      31-DEC-4712     (NULL)          (NULL)      
44444               Academic Rank           Next Academic Rank      Ass.Professor   N               9.7         
44444               Academic Rank           Academic Rank           Student         N               (NULL)      
44444               Academic Rank           Effective Start Date    01-JAN-2017     (NULL)          (NULL)      
44444               Academic Rank           Effective End Date      31-DEC-4712     (NULL)          (NULL)      
44444               Alien Income Forecast   Income_code             R               (NULL)          (NULL)      
44444               Alien Income Forecast   Date                    2017            17              JAN         
44444               Alien Income Forecast   Amount                  50000           Non-Tax         (NULL)      
44444               Alien Income Forecast   Effective Start Date    01-JAN-2017     (NULL)          (NULL)      
44444               Alien Income Forecast   Effective End Date      31-DEC-4712     (NULL)          (NULL)

Sample DDL and DML

CREATE TABLE EMP_EIT_EXT    
(
    EMPLOYEE_NUMBER          VARCHAR2(250),
    Next_Academic_Rank       VARCHAR2(250),
    Max_Academic_Rank        VARCHAR2(250),
    Max_Score                VARCHAR2(250),
    Academic_Rank            VARCHAR2(250),
    Promotion_Rank           VARCHAR2(250),
    ACADEMIC_RANK_START_DATE VARCHAR2(250),
    ACADEMIC_RANK_END_DATE   VARCHAR2(250),
    Income_code              VARCHAR2(250),
    Date_YYYY                VARCHAR2(250),
    Date_MON                 VARCHAR2(250),
    Date_DD                  VARCHAR2(250),
    Amount                   VARCHAR2(250),
    Tax                      VARCHAR2(250),
    INCOME_START_DATE        VARCHAR2(250),
    INCOME_END_DATE          VARCHAR2(250)
);

INSERT INTO EMP_EIT_EXT (EMPLOYEE_NUMBER,Next_Academic_Rank,Max_Academic_Rank,Max_Score,Academic_Rank,Promotion_Rank,ACADEMIC_RANK_START_DATE ,ACADEMIC_RANK_END_DATE,Income_code,Date_YYYY,Date_MON,Date_DD,Amount,Tax,INCOME_START_DATE,INCOME_END_DATE)
VALUES('33333','Professor','Y','10.1','Ass.Professor','Y','01-JAN-2017','31-DEC-4712','P','2017','JAN','01','10000','Tax','01-JAN-2017','31-DEC-4712');
INSERT INTO EMP_EIT_EXT (EMPLOYEE_NUMBER,Next_Academic_Rank,Max_Academic_Rank,Max_Score,Academic_Rank,Promotion_Rank,ACADEMIC_RANK_START_DATE ,ACADEMIC_RANK_END_DATE,Income_code,Date_YYYY,Date_MON,Date_DD,Amount,Tax,INCOME_START_DATE,INCOME_END_DATE)
VALUES('44444','Ass.Professor','N','9.7','Student','N','01-JAN-2017','31-DEC-4712','R','2017','JAN','17','50000','Non-Tax','01-JAN-2017','31-DEC-4712');

I know this can be achieved by UNION or UNION ALL but I want to use UnPivot if possible.

So one problem is, where do the rules come from? How would an UNPIVOT query know that INFO CAT `Next Academic Rank ` belongs to EXTRA INFO `Academic Rank` but `Income_code` belongs to `Alien Income Forecast`? How would it know that `Alien Income Forecast::Date` requires three INFORMATION*n* columns but `Academic Rank:Effective Start Date` has only one? — APC, Apr 17 '17 at 10:11
@APC, that's the same problem i have. there's no reference table for these rules, its all user-defined. Any tips? Thank you! — Migs Isip, Apr 17 '17 at 11:05
The only tip I have is, "don't do this". If you think wrangling the spreadsheet data into [this hideous EAV variant](http://stackoverflow.com/a/876459/146325) is hard, wait until you haveto start writing the queries to return the data. — APC, Apr 17 '17 at 11:51

score -1 · Answer 1 · answered Apr 17 '17 at 09:07

-1

Sure. Just make a bunch off SELECT statements and combine them with UNION. You should be able to figure it out, to solve the exercise.

answered Apr 17 '17 at 09:07

AnoE

8,048
1
21
36

Was hoping to avoid the `UNION`s. – Migs Isip Apr 17 '17 at 09:09
Why, @migsisip? Have they been disallowed for this exercise? – AnoE Apr 17 '17 at 09:18
This is not an exercise. Its a sample of what i want to achieve in my program. – Migs Isip Apr 17 '17 at 09:19
because i already know how to do UNION with this. Is UnPivot not possible? – Migs Isip Apr 17 '17 at 09:26
Oracle has unpivot, yes, but in your example i do not really see the point, since you need to fabricate your new columns either way. The unpivot syntax is in the `select` part of the manual. – AnoE Apr 17 '17 at 09:35
But really, is already knowing how to do it the only reason not to use union? What is the problem with it? – AnoE Apr 17 '17 at 09:36
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/141865/discussion-between-migs-isip-and-anoe). – Migs Isip Apr 17 '17 at 09:37

Unpivot Query with Multiple Columns

1 Answers1