select substr(item, 1, instr(item, '_')-1) type, substr(item, instr(item, '_')+1) item, value from A;
select regexp_substr(item,'[^_]+',1,1) type, sum(decode(regexp_substr(item,'[^_]+',1,2),'AB',value,0)) ab, sum(decode(regexp_substr(item,'[^_]+',1,2),'AC',value,0)) ac from A group by regexp_substr(item,'[^_]+',1,1)
select regexp_substr(item,'[^_]+',1,1) type, sum(decode(regexp_substr(item,'[^_]+',1,2),'AB',value,0)) ab, sum(decode(regexp_substr(item,'[^_]+',1,2),'BC',value,0)) bc from A group by regexp_substr(item,'[^_]+',1,1)
SELECT STR1 AS TYPE, MAX(DECODE(STR2, 'AB', VALUE, 0)) AS AB, MAX(DECODE(STR2, 'BC', VALUE, 0)) AS BC FROM (SELECT REGEXP_SUBSTR(item, '[A-Z]+', 1, 1) AS STR1, REGEXP_SUBSTR(item, '[A-Z]+', 1, 2) AS STR2, value from TEST) GROUP BY STR1
substr(item, 1, instr(item, '_')-1) type,
substr(item, instr(item, '_')+1) item,
value from A;
sum(decode(regexp_substr(item,'[^_]+',1,2),'AB',value,0)) ab,
sum(decode(regexp_substr(item,'[^_]+',1,2),'AC',value,0)) ac
from A group by regexp_substr(item,'[^_]+',1,1)
sum(decode(regexp_substr(item,'[^_]+',1,2),'AB',value,0)) ab,
sum(decode(regexp_substr(item,'[^_]+',1,2),'BC',value,0)) bc
from A group by regexp_substr(item,'[^_]+',1,1)
MAX(DECODE(STR2, 'AB', VALUE, 0)) AS AB,
MAX(DECODE(STR2, 'BC', VALUE, 0)) AS BC
FROM (SELECT REGEXP_SUBSTR(item, '[A-Z]+', 1, 1) AS STR1,
REGEXP_SUBSTR(item, '[A-Z]+', 1, 2) AS STR2,
value
from TEST)
GROUP BY STR1