Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table attributes_list(attribute_name varchar2(50), attribute_value number);
begin
insert into attributes_list values ('product_a_code', 10);
insert into attributes_list values ('product_b_code', 11);
insert into attributes_list values ('product_c_code', 12);
insert into attributes_list values ('product_a_price', 10.99);
insert into attributes_list values ('product_b_price', 20.99);
insert into attributes_list values ('product_c_price', 30.99);
end ;
/
1 rows affected

select 
  T1.attribute_value as product_code
, T2.attribute_value as product_price
from (                                    -- {1}
  select attribute_name, attribute_value 
  from attributes_list
  where attribute_name like '%_code'
) T1 join (                               -- {2}
  select attribute_name, attribute_value 
  from attributes_list
  where attribute_name like '%_price'
) T2
  -- {3} 
  on substr( T1.attribute_name, 1, length( T1.attribute_name ) - length( '_code' ) ) 
   = substr( T2.attribute_name, 1, length( T2.attribute_name ) - length( '_price' ) )
;


PRODUCT_CODE
PRODUCT_PRICE


10
10.99

11
20.99

12
30.99


-- call substr() for finding product names
select 
  case when attribute_name like '%_code' then
    substr( attribute_name, 1, length( attribute_name ) - length( '_code' ) )
  else
    substr( attribute_name, 1, length( attribute_name ) - length( '_price' ) ) 
  end substr_
from attributes_list ;


SUBSTR_


product_a

product_b

product_c

product_a

product_b

product_c