1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| WITH t1 AS (SELECT 'V1, V2' AS a FROM dual),
t2 AS (SELECT '1, 2' AS a FROM dual),
t3 AS (SELECT 'un, deux, trois' AS a FROM dual)
SELECT extractvalue(tab1.COLUMN_VALUE,'/x') c1,
extractvalue(tab2.COLUMN_VALUE,'/x') c2,
extractvalue(tab3.COLUMN_VALUE,'/x') c3
FROM t1, TABLE(xmlsequence(EXTRACT(XMLTYPE('<list><x>'||REPLACE(t1.a,', ','</x><x>')||'</x></list>'),'/list/x'))) tab1,
t2, TABLE(xmlsequence(EXTRACT(XMLTYPE('<list><x>'||REPLACE(t2.a,', ','</x><x>')||'</x></list>'),'/list/x'))) tab2,
t3, TABLE(xmlsequence(EXTRACT(XMLTYPE('<list><x>'||REPLACE(t3.a,', ','</x><x>')||'</x></list>'),'/list/x'))) tab3
C1 C2 C3
V1 1 un
V1 1 deux
V1 1 trois
V1 2 un
V1 2 deux
V1 2 trois
V2 1 un
V2 1 deux
V2 1 trois
V2 2 un
V2 2 deux
V2 2 trois |
Partager