Pivot Multiple Tables with Default Values

Question

Say I have the following tables (sample DDLs and Insert Statements at end of Post):

XX_SAMPLE_ELEMENTS

ELEMENT_NAME                ELEMENT_ENTRY_ID 
--------------------------- ---------------  
Regular Wages               72249260         
Workers Compensation        72249256         
VERTEX                      72249257         
ALC Workers Compensation    72249258         
US_TAX_VERTEX               72249259         
PTO Taken Plan              72523856

XX_ELEMENT_VALUES

INPUT_VALUE_ID  NAME
--------------  ------------------------
30921           Pay Value
455             Pay Value
391             Pay Value
392             Jurisdiction
235             Rate Code
30921           Pay Value
236             Jurisdiction
30922           Continuous Service Date
234             Rate
1425            Pay Value
16180           Pay Value
456             Jurisdiction
394             Calc_Mode
395             Net
233             Pay Value
393             Percentage

XX_ENTRY_VALUES

ELEMENT_ENTRY_VALUE_ID  INPUT_VALUE_ID  ELEMENT_ENTRY_ID    SCREEN_ENTRY_VALUE
----------------------- --------------  -----------------   -------------------
532333350               30921           72249253    
532333351               30922           72249253    
532333352               1425            72249254    
532333353               16180           72249255    
532333354               455             72249256    
532333355               456             72249256            33-000-0000
532333356               391             72249257            
532333357               392             72249257            33-000-0000
532333358               393             72249257            0
532333359               394             72249257            
532333360               395             72249257            
532333361               391             72249258            
532333362               392             72249258            33-065-0000
532333363               393             72249258            0
532333364               394             72249258            
532333365               395             72249258            
532333366               391             72249259            
532333367               392             72249259            33-065-2920
532333368               393             72249259            100
532333369               394             72249259            
532333370               395             72249259            
532333371               233             72249260            
532333372               234             72249260            9.7
532333373               235             72249260            
532333374               236             72249260            
532697988               391             72523856            
532697989               392             72523856            33-065-3190
532697990               393             72523856            0
532697991               394             72523856            
532697992               395             72523856

I need the Data to look like below:

element_name                element_entry_id    input_value_id1 input_value_id2 input_value_id3 input_value_id4 input_value_id5 input_value_id6 screen_entry_value1 screen_entry_value2 screen_entry_value3 screen_entry_value4 screen_entry_value5 screen_entry_value6 name1           name2           name3           name4           name5           name6
-------------------------   ----------------    --------------- --------------- --------------- --------------- --------------- --------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------   --------------  --------------- --------------  --------------  --------------
ALC Workers Compensation    72249258            393             395             394             391             392             392             0                   NULL                NULL                NULL                33-065-0000         33-065-3190         Percentage      Net             Calc_Mode       Pay Value       Jurisdiction    Jurisdiction
PTO Taken Plan              72523856            392             391             393             395             394             NULL            33-065-3190         NULL                0                   NULL                NULL                NULL                Jurisdiction    Pay Value       Percentage      Net             Calc_Mode       NULL
Regular Wages               72249260            234             236             235             233             NULL            NULL            9.7                 NULL                NULL                NULL                NULL                NULL                Rate            Jurisdiction    Rate Code       Pay Value       NULL            NULL
US_TAX_VERTEX               72249259            391             394             393             395             392             NULL            NULL                NULL                100                 NULL                33-065-2920         NULL                Pay Value       Calc_Mode       Percentage      Net             Jurisdiction    NULL
VERTEX                      72249257            393             395             391             392             394             NULL            0                   NULL                NULL                33-000-0000         NULL                NULL                Percentage      Net             Pay Value       Jurisdiction    Calc_Mode       NULL
Workers Compensation        72249256            455             456             NULL            NULL            NULL            NULL            NULL                33-000-0000         NULL                NULL                NULL                NULL                Pay Value       Jurisdiction    NULL            NULL            NULL            NULL

So far, i have the below query:

SELECT  lmnt.element_name
    ,   lmnt.element_entry_id
    ,   nval.input_value_id
    ,   nval.screen_entry_value
    ,   eval.name
FROM    XX_SAMPLE_ELEMENTS lmnt
    ,   XX_ENTRY_VALUES    nval
    ,   XX_ELEMENT_VALUES  eval
where   lmnt.element_entry_id = nval.element_entry_id
and     eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID
order by lmnt.element_name;

Question

How do I Pivot the columns input_value_id, screen_entry_value and name, when the Elements do not have a fixed number of values? (i.e. PTO Taken Plan has 5 Elements (Jurisdiction, Pay Value, Percentage, Net, Calc_Mode) while Workers Compensation only has 2 (Pay Value and Jurisdiction)).

Notes

I cannot modify the table structures as these tables are priopietary products.
SQL is the preferred approach, but if no solution in SQL is possible, PL/SQL would be fine.
Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

DDL and Insert Statements

CREATE TABLE XX_SAMPLE_ELEMENTS
(
    ELEMENT_NAME        VARCHAR2(100)
,   ELEMENT_ENTRY_ID    NUMBER   
);

CREATE TABLE XX_ELEMENT_VALUES
(
    INPUT_VALUE_ID      NUMBER
,   NAME                VARCHAR2(100)
);

CREATE TABLE XX_ENTRY_VALUES
(
    ELEMENT_ENTRY_VALUE_ID  NUMBER
,   INPUT_VALUE_ID          NUMBER
,   ELEMENT_ENTRY_ID        NUMBER
,   SCREEN_ENTRY_VALUE      VARCHAR2(100)
);

INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('Regular Wages', '72249260'); 
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('Workers Compensation','72249256');      
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('VERTEX','72249257');        
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('ALC Workers Compensation','72249258');      
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('US_TAX_VERTEX','72249259');         
INSERT INTO XX_SAMPLE_ELEMENTS (ELEMENT_NAME, ELEMENT_ENTRY_ID) VALUES ('PTO Taken Plan','72523856');        

Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (455,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (391,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (392,'Jurisdiction');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (235,'Rate Code');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (30921,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (236,'Jurisdiction');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (30922,'Continuous Service Date');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (234,'Rate');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (1425,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (16180,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (456,'Jurisdiction');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (394,'Calc_Mode');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (395,'Net');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (233,'Pay Value');
Insert into XX_ELEMENT_VALUES (INPUT_VALUE_ID,NAME) values (393,'Percentage');


INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333350', '30921',  '72249253', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333351', '30922',  '72249253', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333352', '1425',   '72249254', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333353', '16180',  '72249255', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333354', '455',    '72249256', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333355', '456',    '72249256', '33-000-0000');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333356', '391',    '72249257', NULL);
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333357', '392',    '72249257', '33-000-0000');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333358', '393',    '72249257', '0');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333359', '394',    '72249257', NULL);       
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333360', '395',    '72249257', NULL);       
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333361', '391',    '72249258', NULL);       
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333362', '392',    '72249258', '33-065-0000');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333363', '393',    '72249258', '0');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333364', '394',    '72249258', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333365', '395',    '72249258', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333366', '391',    '72249259', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333367', '392',    '72249259', '33-065-2920');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333368', '393',    '72249259', '100');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333369', '394',    '72249259', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333370', '395',    '72249259', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333371', '233',    '72249260', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333372', '234',    '72249260', '9.7');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333373', '235',    '72249260', NULL);       
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532333374', '236',    '72249260', NULL);       
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697988', '391',    '72523856', NULL);       
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697989', '392',    '72523856', '33-065-3190');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697990', '393',    '72523856', '0');
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697991', '394',    '72523856', NULL);        
INSERT INTO XX_ENTRY_VALUES(ELEMENT_ENTRY_VALUE_ID,INPUT_VALUE_ID, ELEMENT_ENTRY_ID, SCREEN_ENTRY_VALUE) VALUES ('532697992', '395',    '72523856', NULL);

krokodilko krokodilko · Accepted Answer · 2017-08-10T07:12:45

This query can be a good start as a base for furhter experimens:

With src As (
    SELECT  lmnt.element_name
        ,   lmnt.element_entry_id
        ,   nval.input_value_id
        ,   nval.screen_entry_value
--        ,   eval.name 
        ,   row_number() over (partition by lmnt.element_name, lmnt.element_entry_id
                               order by lmnt.element_name  ) rn
    FROM    XX_SAMPLE_ELEMENTS lmnt
        ,   XX_ENTRY_VALUES    nval
        ,   XX_ELEMENT_VALUES  eval
    where   lmnt.element_entry_id = nval.element_entry_id
    and     eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID          
    order by lmnt.element_name
)


SELECT * FROM src
PIVOT (
    max( input_value_id ) As input_value,
    min( screen_entry_value ) as screen_entry_value
    FOR (rn)
    IN (1,2,3,4,5)
)

Pivot Multiple Tables with Default Values

1 Answers