oracle-select-from-EAV-entity–attribute–value

test data:

CREATE TABLE ORDERS (
  order_id         NUMBER,
  attribute_name   VARCHAR2(32),
  text_value       VARCHAR2(32),
  number_value     NUMBER
);

INSERT INTO ORDERS VALUES (1, 'Currency', 'USD', NULL);
INSERT INTO ORDERS VALUES (1, 'Country', 'US',  NULL);
INSERT INTO ORDERS VALUES (1, 'Amount', NULL, 1000);

INSERT INTO ORDERS VALUES (2, 'Currency', 'SGD', NULL);
INSERT INTO ORDERS VALUES (2, 'Country', 'SG',  NULL);
INSERT INTO ORDERS VALUES (2, 'Amount', NULL, 2000);

select 'PolicyCode', 'Currency' and 'Region' for each row :

SELECT order_id
  , MAX(CASE WHEN attribute_name = 'Currency' THEN text_value ELSE '' END) AS CurrencyCode
  , MAX(CASE WHEN attribute_name = 'Country' THEN text_value ELSE '' END) AS CountryCode
  , MAX(CASE WHEN attribute_name = 'Amount' THEN number_value ELSE NULL END) AS AmountV
FROM ORDERS
GROUP BY order_id

http://sqlfiddle.com/#!4/5b8e9/10

with DECODE: http://sqlfiddle.com/#!4/7703f/13

gary-liguoliang/oracle-select-from-EAV-entity–attribute–value.md