CODEWITHSUNDEEP
sqloracle-database
Geralt
Geralt

Reputation: 180

Oracle - How to turn rows into columns "in pairs"

There is a table that basically lists field names and their values, but each attribute and value is in a separate row. In the example below, the table lists product codes and their prices.

create table attributes_list(attribute_name varchar2(50), attribute_value number);
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);

original table

Is it possible to turn these rows into columns, but in a way where I can link the prices to the product codes?

resulting table

PS. I know the title is not ideal, but I couldn't think of a better way to make this question. Suggestions are welcome.

Upvotes: 1

Views: 292

Answers (3)

stefan
stefan

Reputation: 2252

Write a query that {1} finds all rows whose ATTRIBUTE_NAMEs ends in '_code', and {2} finds all rows whose ATTRIBUTE_NAMEs ends in '_price'. {3} JOIN the 2 result sets ON the product names, which can be found via SUBSTR().

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' ) )
;

-- result
   PRODUCT_CODE    PRODUCT_PRICE 
_______________ ________________ 
             10            10.99 
             11            20.99 
             12            30.99

DBfiddle here.

You can use REGEXP_REPLACE() - as in @GMB's answer - for finding the product names in the ON clause (instead of SUBSTR()).

Upvotes: 0

GMB
GMB

Reputation: 222482

If, as shown in your sample data, column attribute_name has a fixed format made of a product and an attribute separated by an underscore (without an underscore in the attribute name), you can use regexes and conditional aggregation, like so:

select
    regexp_replace(attribute_name, '_[^_]+$', '') product_name,
    max(case when regexp_substr(attribute_name, '[^_]+$') = 'code'  then attribute_value end) product_code,
    max(case when regexp_substr(attribute_name, '[^_]+$') = 'price' then attribute_value end) product_price
from attributes_list 
group by regexp_replace(attribute_name, '_[^_]+$', '')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need at least three columns:

create table attributes_list (
    product varchar2(50),
    attribute_name varchar2(50),
    attribute_value number
);
insert into attributes_list values ('a', 'code', 10);
insert into attributes_list values ('b', 'code', 11);
. . .

Then you can use conditional aggregation:

select max(case when attribute_name = 'code' then value end) as code,
       max(case when attribute_name = 'price' then value end) as price
from attributes_list
group by product;

Upvotes: 1

Related Questions