Q55925050, Oracle - rextester

Language: Layout:

with value( value_id, value_tx, field_name, version_id ) as
(
 select 1 ,'Yes'  ,'Adult'        ,1 from dual union all
 select 2 ,'18'   ,'Age'          ,1 from dual union all
 select 3 ,'Black','Eye_Color'    ,1 from dual union all
 select 4 ,'Yes'  ,'Is_Adult'     ,2 from dual union all
 select 5 ,'25'   ,'Years_old'    ,2 from dual union all
 select 6 ,'Brown','Color_of_Eyes',2 from dual    
), Submitted( version_id, version_name ) as
(
 select 1 ,'TEST_RUN' from dual union all
 select 2 ,'REAL_RUN' from dual    
)    
select *
  from
  (
    select s.version_name as "Submitted_Name", v.value_Tx, 
           decode(v.field_name,'Adult','Is_Adult','Age','Years_old','Eye_Color','Color_of_Eyes',v.field_name) as field_name
      from value v
      join Submitted s 
        on s.version_id = v.version_id
     group by v.value_Tx, s.version_name,
              decode(v.field_name,'Adult','Is_Adult','Age','Years_old','Eye_Color','Color_of_Eyes',v.field_name)
  ) 
  pivot(
         max(value_tx) for field_name in ( 'Is_Adult' as "Adult?", 'Years_old' as "Age", 'Color_of_Eyes' as "Eye_Color" )
       )\\
       
with value( value_id, value_tx, field_name, version_id ) as
(
 select 1 ,'Yes'  ,'Adult'        ,1 from dual union all
 select 2 ,'18'   ,'Age'          ,1 from dual union all
 select 3 ,'Black','Eye_Color'    ,1 from dual union all
 select 4 ,'Yes'  ,'Is_Adult'     ,2 from dual union all
 select 5 ,'25'   ,'Years_old'    ,2 from dual union all
 select 6 ,'Brown','Color_of_Eyes',2 from dual    
), Submitted( version_id, version_name ) as
(
 select 1 ,'TEST_RUN' from dual union all
 select 2 ,'REAL_RUN' from dual    
)    
select *
  from
  (
    select s.version_name as "Submitted_Name", v.value_Tx, mod(v.value_id,3) as value_id
      from value v
      join Submitted s 
        on s.version_id = v.version_id
     group by v.value_Tx, s.version_name, mod(v.value_id,3)
  ) 
  pivot(
         max(value_tx) for value_id in ( 1 as "Adult?", 2 as "Age", 0 as "Eye_Color" )
       )


absolute service time: 1,8 sec