1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
SET echo ON
SET linesize 200
SET pagesize 100
COLUMN list_c1 format A5
DROP TABLE t;
CREATE TABLE t (c1 varchar2(20), c2 varchar2(20));
INSERT INTO t VALUES ('A,B,C,D','Blabla');
INSERT INTO t VALUES ('E,F,G','Blibli');
INSERT INTO t VALUES ('K,M,O,P,T,R','Blublu');
commit;
SELECT t.c2, x.column_value AS list_c1
FROM t
CROSS JOIN TABLE(
cast(multiset(
SELECT substr( ','||c1||',',
instr( ','||c1||',', ',', 1, rownum )+1,
instr( ','||c1||',', ',', 1, rownum+1 )
-instr( ','||c1||',', ',', 1, rownum )-1
)
FROM dual
connect BY level <= length(c1)-length(REPLACE(c1,',',''))+1
) AS sys.odcivarchar2list )
) x; |
Partager