CREATE TABLE:-
=============
CREATE TABLE input_data AS
(SELECT 100 claim_id,'|A=10|B=20|C=30|' textdata FROM DUAL UNION ALL
SELECT 101,'|A=40|D=50|' FROM DUAL UNION ALL
SELECT 102,'|B=60|E=70|' FROM DUAL);
Tried queries:-
=============
1)XMLTABLE with PIVOT(Inline View):-
====================================
SELECT *
FROM (SELECT claim_id,
SUBSTR(object_val,1,INSTR(object_val,'=')-1) str,
TO_NUMBER(SUBSTR(object_val,INSTR(object_val,'=')+1)) valu
FROM input_data,
XMLTABLE('/data/e'
PASSING XMLTYPE('<data><e>'||REPLACE(TRIM('|' FROM textdata),'|','</e><e>')||'</e></data>')
COLUMNS object_val VARCHAR2(30) PATH '.'))
PIVOT(MAX(valu) FOR str IN('A' AS val1,'B' AS val2,'C' AS val3,'D' AS val4,'E' AS val5));
SELECT *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28496
Session ID: 109 Serial number: 18559
2)XMLTABLE with PIVOT(With clause):-
====================================
WITH qry1 AS(SELECT claim_id,
SUBSTR(object_val,1,INSTR(object_val,'=')-1) str,
TO_NUMBER(SUBSTR(object_val,INSTR(object_val,'=')+1)) valu
FROM input_data,
XMLTABLE('/data/e'
PASSING XMLTYPE('<data><e>'||REPLACE(TRIM('|' FROM textdata),'|','</e><e>')||'</e></data>')
COLUMNS object_val VARCHAR2(30) PATH '.'))
SELECT * FROM qry1
PIVOT(MAX(valu) FOR str IN('A' AS val1,'B' AS val2,'C' AS val3,'D' AS val4,'E' AS val5));
WITH qry1 AS(SELECT claim_id,
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28541
Session ID: 109 Serial number: 18581
3)XMLTABLE with Decode:-
=======================
SELECT claim_id,
MAX(DECODE(str,'A',valu)) AS val1,
MAX(DECODE(str,'B',valu)) AS val2,
MAX(DECODE(str,'C',valu)) AS val3,
MAX(DECODE(str,'D',valu)) AS val4,
MAX(DECODE(str,'E',valu)) AS val5
FROM
(SELECT claim_id,
SUBSTR(object_val,1,INSTR(object_val,'=')-1) str,
TO_NUMBER(SUBSTR(object_val,INSTR(object_val,'=')+1)) valu
FROM input_data,
XMLTABLE('/data/e'
PASSING XMLTYPE('<data><e>'||REPLACE(TRIM('|' FROM textdata),'|','</e><e>')||'</e></data>')
COLUMNS object_val VARCHAR2(30) PATH '.'))
GROUP BY claim_id;
OUTPUT:-
======
CLAIM_ID VAL1 VAL2 VAL3 VAL4 VAL5
-------- ---- ---- ---- ---- ----
100 10 20 30
102 60 70
101 40 50
4)Hierarchy(Connect BY) with PIVOT:-
===================================
SELECT *
FROM (SELECT claim_id,
SUBSTR(REGEXP_SUBSTR(TRIM( '|' FROM textdata),'[^|]+', 1, LEVEL),1,1) str,
TO_NUMBER(SUBSTR(REGEXP_SUBSTR(TRIM( '|' FROM textdata),'[^|]+', 1, LEVEL),INSTR(REGEXP_SUBSTR(TRIM( '|' FROM textdata),'[^|]+', 1, LEVEL),'=')+1)) valu
FROM input_data
CONNECT BY LEVEL <= REGEXP_COUNT(TRIM('|' FROM textdata), '[^|]+')
AND PRIOR claim_id = claim_id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL)
PIVOT(MAX(valu) FOR str IN('A' AS val1,'B' AS val2,'C' AS val3,'D' AS val4,'E' AS val5));
OUTPUT:-
=======
CLAIM_ID VAL1 VAL2 VAL3 VAL4 VAL5
-------- ---- ---- ---- ---- ----
100 10 20 30
102 60 70
101 40 50