Convert Horizontal CSV Template to a Tabular Format

Question

Disclaimer: Please bear with the long, detailed question. I want to make the requirement as clear as possible.

The below seeded table (SAMPLE_EXTRA_INFO) stores an employee's Extra Information:

EIT_ID  EMPLOYEE_NUMBER     EXTRA_INFORMATION       INFORMATION_CATEGORY    INFORMATION1    INFORMATION2    INFORMATION3 .... INFORMATION10
------  ------------------- ---------------------   ----------------------  --------------  -------------   ------------      --------------
1       11111               Academic Rank           Next Academic Rank      Professor       Y               10.5              (NULL)      
2       11111               Academic Rank           Academic Rank           Ass.Professor   N               (NULL)            (NULL)  
3       11111               Academic Rank           Effective Start Date    01-JAN-2017     (NULL)          (NULL)            (NULL)
4       11111               Academic Rank           Effective End Date      31-DEC-4712     (NULL)          (NULL)            (NULL)
5       11111               Alien Income Forecast   Income_code             P               (NULL)          (NULL)            (NULL)    
6       11111               Alien Income Forecast   Date                    2017            01              DEC               (NULL)      
7       11111               Alien Income Forecast   Amount                  10000           Tax             (NULL)            (NULL)          
8       11111               Alien Income Forecast   Effective Start Date    01-JAN-2017     (NULL)          (NULL)            (NULL)      
9       11111               Alien Income Forecast   Effective End Date      31-DEC-4712     (NULL)          (NULL)            (NULL)      
10      22222               Academic Rank           Next Academic Rank      Master          N               11.5              (NULL)            
11      22222               Academic Rank           Academic Rank           Professor       Y               (NULL)            (NULL)  
12      22222               Academic Rank           Effective Start Date    01-JAN-2017     (NULL)          (NULL)            (NULL)
13      22222               Academic Rank           Effective End Date      31-DEC-4712     (NULL)          (NULL)            (NULL)
14      22222               Alien Income Forecast   Income_code             X1              (NULL)          (NULL)            (NULL)    
15      22222               Alien Income Forecast   Date                    2017            01              APR               (NULL)    
16      22222               Alien Income Forecast   Amount                  100000000       Tax-Free        (NULL)            (NULL)          
17      22222               Alien Income Forecast   Effective Start Date    01-JAN-2017     (NULL)          (NULL)            (NULL)      
18      22222               Alien Income Forecast   Effective End Date      31-DEC-4712     (NULL)          (NULL)            (NULL)      

All Information Columns have the data type VARCHAR2, but some store dates (like in the above sample).

This table is being populated by a seeded API Package (SAMPLE_PKG.CREATE_EXTRA_INFO).

Sample Procedure Specification

SAMPLE_PKG.CREATE_EXTRA_INFO
(
    P_EMPLOYEE_NUMBER       VARCHAR2
,   P_EXTRA_INFORMATION     VARCHAR2
,   P_INFORMATION_CATEGORY  VARCHAR2
,   P_INFORMATION1          VARCHAR2
,   P_INFORMATION2          VARCHAR2
,   P_INFORMATION3          VARCHAR2
,   P_INFORMATION4          VARCHAR2
,   P_INFORMATION5          VARCHAR2
,   P_INFORMATION6          VARCHAR2
,   P_INFORMATION7          VARCHAR2
,   P_INFORMATION8          VARCHAR2
,   P_INFORMATION9          VARCHAR2
,   P_INFORMATION10         VARCHAR2
);

I am tasked to create an upload process that would use a CSV template and interface information to the API. The below is a sample of the CSV Template:

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

I'm planning to use an External Table (SAMPLE_EXTRA_INFO_EXT) to read the CSV Template and pass the information to the API.

Create Table SAMPLE_EXTRA_INFO_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)
)
organization external ( type oracle_loader
                        default directory EXT_TAB_DATA
                        access parameters
                        (
                            records delimited by newline
                            skip 1
                            fields terminated by ','
                            optionally enclosed by '"' LRTRIM
                            missing field values are null                            
                        )
                        location(EXT_TAB_DATA: 'Demographic_file_10APR2017.csv')
                       ) reject limit unlimited
                       ;

And use something like the code block below (please note the comments):

DECLARE

    CURSOR EXT_CUR IS
    SELECT  EMPLOYEE_NUMBER    
        ,   NEXT_ACADEMIC_RANK        -- Academic Rank (Next Academic Rank) INFORMATION1    
        ,   MAX_ACADEMIC_RANK         -- Academic Rank (Next Academic Rank) INFORMATION2    
        ,   MAX_SCORE                 -- Academic Rank (Next Academic Rank) INFORMATION3  
        ,   ACADEMIC_RANK             -- Academic Rank (Academic Rank) INFORMATION1    
        ,   PROMOTION_RANK            -- Academic Rank (Academic Rank) INFORMATION2     
        ,   ACADEMIC_RANK_START_DATE  -- Academic Rank (Effective Start Date) INFORMATION1
        ,   ACADEMIC_RANK_END_DATE    -- Academic Rank (Effective End Date) INFORMATION1
        ,   INCOME_CODE               -- Alien Income Forecast (Income_code) INFORMATION1
        ,   DATE_YYYY                 -- Alien Income Forecast (Date) INFORMATION1
        ,   DATE_MON                  -- Alien Income Forecast (Date) INFORMATION2 
        ,   DATE_DD                   -- Alien Income Forecast (Date) INFORMATION3 
        ,   AMOUNT                    -- Alien Income Forecast (Amount) INFORMATION1
        ,   TAX                       -- Alien Income Forecast (Amount) INFORMATION2 
        ,   INCOME_START_DATE         -- Alien Income Forecast (Effective Start Date) INFORMATION1 
        ,   INCOME_END_DATE           -- Alien Income Forecast (Effective End Date) INFORMATION1                          
    FROM    SAMPLE_EXTRA_INFO_EXT;    -- this is the external table

BEGIN

    FOR EXT_REC IN EXT_CUR LOOP 

        SAMPLE_PKG.CREATE_EXTRA_INFO
        (
            P_EMPLOYEE_NUMBER       => EXT_REC.EMPLOYEE_NUMBER      
        ,   P_EXTRA_INFORMATION     => EXT_REC.EXTRA_INFORMATION    -- This is my problem, how do i make it vertical?
        ,   P_INFORMATION_CATEGORY  => EXT_REC.INFORMATION_CATEGORY 
        ,   P_INFORMATION1          => EXT_REC.INFORMATION1         
        ,   P_INFORMATION2          => EXT_REC.INFORMATION2         
        ,   P_INFORMATION3          => EXT_REC.INFORMATION3         
        ,   P_INFORMATION4          => EXT_REC.INFORMATION4         
        ,   P_INFORMATION5          => EXT_REC.INFORMATION5         
        ,   P_INFORMATION6          => EXT_REC.INFORMATION6         
        ,   P_INFORMATION7          => EXT_REC.INFORMATION7         
        ,   P_INFORMATION8          => EXT_REC.INFORMATION8         
        ,   P_INFORMATION9          => EXT_REC.INFORMATION9         
        ,   P_INFORMATION10         => EXT_REC.INFORMATION10        
        );

    END LOOP;

END;

Here are my problems:

1. The template is "horizontal", but the API is "vertical".
2. Not all Extra Information use the same Information Columns (some use column INFORMATION1 - 3, some use only column INFORMATION1 ).

With that said, how can i transform the template above into a valid format that the API can use?

I was thinking of using Pivot or UnPivot in the External Table but i'm not quite sure on how to approach the requirement. A Pure SQL approach is preferred, but using PL/SQL would be fine.

Oracle Version

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Answer 1

It looks to me you need to select many times from the same table, using the different column depending on which info you are gathering. If your template is static, here is what it gives for the code of your EXT_CUR cursor:

CURSOR EXT_CUR IS
          select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Next Academic Rank'   INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Academic Rank'        INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective Start Date' INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective End Date '  INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Income_code '         INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Date        '         INFORMATION_CATEGORY, [... fetching various columns]  from SAMPLE_EXTRA_INFO_EXT
union all select employee_number, [and it goes on for whatever line your API needs]

Here is working example to illustrate

with t as (  
select '33333  ' EMPLOYEE_NUMBER
, 'Professor   ' "Next Academic Rank "
, 'Y           ' "Max Academic Rank  "
, '10.1  '       "Max Score  "
,'Ass.Professor' "Academic Rank "
,'Y           '  "Promotion Rank"
,'01-JAN-2017 '  "ACADEMIC_RANK_START_DATE"
,'31-DEC-4712 '  "ACADEMIC_RANK_END_DATE"
,'P         '    "Income_code "
,'2017      '    "Date(YYYY)"
,' JAN      '    "Date(MON)"
,'   01     '    "Date(DD)"
,'  10000  '     "  Amount "
,' Tax     '     "Tax?"
,'01-JAN-2017  ' "INCOME_START_DATE  "
,' 31-DEC-4712 ' " INCOME_END_DATE"
from dual
union select '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    ' from dual
)         select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Next Academic Rank'   INFORMATION_CATEGORY,  "Next Academic Rank "         INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Academic Rank'        INFORMATION_CATEGORY,  "Academic Rank "              INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective Start Date' INFORMATION_CATEGORY,  "ACADEMIC_RANK_START_DATE"    INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'Academic Rank' EXTRA_INFORMATION,   'Effective End Date '  INFORMATION_CATEGORY,  "ACADEMIC_RANK_END_DATE"      INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Income_code '         INFORMATION_CATEGORY,  "Income_code "                INFORMATION1 , null         INFORMATION2 , null       INFORMATION3 from t 
union all select employee_number, 'AlienIF'       EXTRA_INFORMATION,   'Date        '         INFORMATION_CATEGORY,  "Date(YYYY)"                  INFORMATION1 , "Date(MON)"  INFORMATION2 , "Date(DD)" INFORMATION3 from t 
order by 1, 2, 3

leading to

If your template changes with time then it becomes a different problem. You must learn how to build a cursor with dynamic PL/SQL. There's plenty of resources here on SO. That's a pretty big chunk of work, but uses same idea as above.